Thanks again, and another question

[Andre]

Thanks to all your help, I'm much farther on the project than I had
been before I found this newsgroup. However, it seems that everytime
I thought I understood how things work, it doesn't work the way I
thought it would. Here's my dilemma, I have a dropdown list with text
selections for the user. I am now able to recognize which item is
selected and make that into a document variable. Now I need to create
another variable whose value is dependent on what is selected. Thanks
to your help, I'm now able to use CASE statement. But here's what
happened. I put a watch on the variable BeltType to ensure that I'm
looking at the right one. I use INSTR to search for a string within
the BeltType document variable. The BeltType variable is changing
according to what the user selects, but the Case statement isn't doing
its job. It's not changing the variable BeltTypePrice to the
appropriate one. What am I doing wrong in the Case statement?

Thank you very much,

Andre S

Sub BeltType()
'
' BeltType Macro
' Macro created 9/7/2003 by AndreS
'
Dim BeltType

ActiveDocument.Variables("BeltType").Value =
ActiveDocument.FormFields("BeltType").Result
BeltType = ActiveDocument.FormFields("BeltType").Result

Select Case BeltType
Case InStr(BeltType, "WMX-2222") > 0
BeltTypePrice = 0.75
Case InStr(BeltType, "WMX-2222 Oil") > 0
BeltTypePrice = 0.825
Case InStr(BeltType, "330#") > 0
BeltTypePrice = 0.775
Case InStr(BeltType, "3332 Oil") > 0
BeltTypePrice = 0.875
Case Else ' should never get here
MsgBox "Invalid BeltType"
End Select

ActiveDocument.Variables("BeltTypePrice").Value = BeltTypePrice

End Sub

 

[Mark Tangard]

Hi Andre,

The Case statements aren't valid. Each value tested needs
needs to be a numeric or string expression. What you have
instead, if anything, is *logical* expressions -- all your
Cases evaluate to True or False.

I can't see the full prior explanation of your project
(this is one reason it helps to post follow-ups to the
*same* thread!), but if the belt types are the items in
the dropdown, the Case statements should read simply:

Case "WMX-2222"
BeltTypePrice = 0.75
Case "WMX-2222 Oil"
BeltTypePrice = 0.825
:
:
and so on.

If the belt types are *part* of the text of the items in
the dropdown, then you still need to produce a *string*
expression for each, not a test of whether they're found
in the dropdown items. That's pretty straightforward,
but it may require you to create and test a different
variable, say BT:

If InStr(BeltType, "WMX-2222") > 0 Then BT = "WMX-2222"
If InStr(BeltType, "WMX-2222 Oil") > 0 Then BT = "WMX-2222 Oil"
:
:
and so on

and then the first statement in your Case block would be

Select Case BT

Hope this helps a little.

 

[Andre]

Hello Mark,

Your comments are exactly what I need, while the books and VB help
showed me how a case statement is done, it wasn't what I wanted. Your
explanation on how a case statement actually works and why my
statements don't work are what's missing in my resources. Thank you
very much, and I hope to learn VBA as well as most of you guys in
here. It seems to be a pretty good method on making a tool (MS
Office) work exactly the way I want it to. BTW, your assumptions on
how my code works is correct.

Thank you again, and I'll keep trying. I'll keep the thread too.


Andre S