Solved: Referencing items in a drop down menu

[dbhaley]

I'm very new to VBA (but not MS Office) and this forum so my apologies if this question is a bit sophmoric or if I am posting this in the wrong place.

I have 6 drop down menus and I would like for a different action to take place depending on the dropdown items that the user has selected. How do I reference the user's selections in my "if" statement? I have tried a couple of things and continue to get errors.

Also, does anyone have any examples of this type of code?

Thank you very much for all of your help. This website is such a great learning tool!

[CreganTur]

If this is on a UserForm, then you can refer to an object's value very easily. First, you need to use the Me keyword- this refers to the current active form- it's a shortcut to referencing the form object.

When you type in "Me." you will get a selection box from Intellisense that shows you all of the objects on the form and the available methods and properties that you can control via VBA.

To reference your listrbox's value use:
Me.ListBoxName
Where ListBoxName is the actual name of the listbox object. This will automatically pull the value of the first column of the listbox. If you have multiple columns, or need the value from a specific column, then you will need to use the .columns method (see Help).

To use this as a part of your If conditionals, you can do something like:
If Me.ListBoxName > 10 Then
MsgBox "greater than 10"
ElseIf Me.ListBoxName < 9 Then
MsgBox "less than 9"
Else
MsgBox "value is eithre 9 or 10"
End If

HTH:thumb

[dbhaley]

Randy,
Thanks for your help. I am getting the following error:

"Invalid use of Me keyword"

Here is my code:
Sub InsertLink()

If Me.LoanType = "Consumer" Then

Selection.GoTo What:=wdGoToBookmark, Name:="RequiredDocs"

Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"G:\Commercial Loans\CLOSING WORD DOCUMENTS\Mortgages\Modification of Mortgage .doc" _
, TextToDisplay:= _
"Mortgage Modification"
Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"G:\Commercial Loans\CLOSING WORD DOCUMENTS\Mortgages\Modification of Mortgage .doc" _
, TextToDisplay:="Consumer"

ElseIf Me.LoanType = "Consumer" Then

Selection.GoTo What:=wdGoToBookmark, Name:="RequiredDocs"

Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"G:\Commercial Loans\CLOSING WORD DOCUMENTS\Mortgages\Modification of Mortgage .doc" _
, TextToDisplay:= _
"Mortgage Modification"
Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"G:\Commercial Loans\CLOSING WORD DOCUMENTS\Mortgages\Extension of Mtg HOCL .doc" _
, TextToDisplay:="Commercial"

Else: MsgBox "Please select a loan type"

End If

End Sub

[dbhaley]

In the end I would like for the macro to do this


If Dropdown1 = "String1" and
Dropdown2 = "String1" and
Dropdown3 = "String1" and
Dropdown4 = "String1" Then
[statement of actions]

Elseif Dropdown1 = "String2" and
Dropdown2 = "String1" and
Dropdown3 = "String1" and
Dropdown4 = "String1" Then
[statement of actions]

Elseif [other conditions]
[statement of actions]
End If


I am able to code all of the actions I want to do, but I can't get the if statements to work. I hope this helps.

[dbhaley]

I just realized what you meant by UserForm. I am not familiar with UserForms. I would like for the dropdown menus to appear on a word document for simplicity. The user will select the appropriate menu items and run the macro. I would like to reference the dropdown menus within the word document. I hope that helps clarify.

[CreganTur]

I just realized what you meant by UserForm

Yeah, the Me keyword method I discussed earlier is only for UserForms.

I don't have experience placing listboxes directly onto documents, so I'll have to pass this question to someone else.

[lucas]

are they activeX dropdowns or formfield dropdowns or what?

[fumei]

1. they are most likely formfields, but we do indeed need to know if they are ActiveX controls, or formfields.

2. if they are formfields, then you need to test against the Result.

3. It would likely be better to use Select Case, rather than a bunch of If...Then statements.

Assuming DropDown1 is a formfield (as that is a default name for a formfield...an ActiveX dropdown control defaults to Combobox1), then:

Select Case ActiveDocument.Formfields("Dropdown1").Result
Case "String1"
If Dropdown2 = "String1" And
Dropdown3 = "String1" And
Dropdown4 = "String1" Then
[statement of actions]
End If
Case "String2"
If Dropdown2 = "String1" And
Dropdown3 = "String1" And
Dropdown4 = "String1" Then
[statement of actions]
End If
...etc.
Case Else
' whatever
End Select

[dbhaley]

They are formfields. Now I have to teach myself about the case statement while I try to figure this out. Thanks!

[dbhaley]

I created two dropdown formfields named LoanType and SecuredBy in order to test the select case way to do this, but when I ran the macro it didn't take the action I wanted. Here was my test of the select case script.

Sub DropDownCaseTest()
Select Case ActiveDocument.FormFields("LoanType").Result
Case "Consumer"
If SecuredBy = "Unsecured" Then

MsgBox "Consumer Unsecured"

End If
Case "Commercial"
If SecuredBy = "Unsecured" Then

MsgBox "Commercial Unsecured"

End If

Case Else
MsgBox "Select an item from each menu"

End Select

End Sub

Why wouldn't the message box pop up? before I go further, I would like to know if there will be a problem with the actions I want to execute.

Also, is this an effective way to have multiple conditions for each case?

Case "Consumer"
If SecuredBy = "Unsecured" And _
NoOfSigners = "Two" Then

MsgBox "Consumer Unsecured, Two Signers"

End If

[dbhaley]

Figured it out. My references were incorrect. I have got it working. Thanks for all the help.

[fumei]

Ah, yes, your references were not qualified. VBA had no way to know what:

If SecuredBy = "Unsecured"

meant. It would parse SecuredBy and, to VBA, try to resolve a variable with the name of SecuredBy.

I am sure you came up with:

If ActiveDocument.Formfields("SecuredBy").Result _
= "Unsecured"



And good on you for figuring it out. You are well on your way. Thank you for posting back.

Gotta ask. Are you using Option Explicit in your code modules? I suspect not. If you are not, start doing so now. It will save you a lot of headaches in the long run.

In fact, if you had Option Explicit, you would not have been able to even run the procedure, as VBA would have yelled at you. Which is why we use Option Explicit.

[dbhaley]

No, and I'm not at all familiar with that. Do you have any links explaining?

[lucas]

run the sub named a in the standard module of the file attached.

You will receive an error that a variable is not defined. Look at the code and uncomment the line that says


dim onname as string

it runs ok then. The reason that it would not run is as Gerry pointed out, with Option explicit at the top of each module, including thisworkook and userform modules, you will be prompted when errors like this occur and you can make them right.


likewise, you can leave the line of code commented and delete or comment the Option explicit line and it wil run but an error is occuring which you are not being notified about.

[dbhaley]

OK, got it. That makes a lot of sense. I will figure out how to turn it on and use it from now on. Thanks!

[fumei]

Good. Again, trust me, having to declare variables will save you great pain over time.

I once spent HOURS trying to figure out why something would not work. I declared a variable:

Dim strClient As String

except in the code, I used strCleint in one place.

Option Explicit catches both syntax and spelling errors!.

WITH Option Explicit (and using the incorrect spelling):

VBA yells "What the heck is THAT? I do not know what that is, and I am not going to do anything until you tell what that is."

WITHOUT Option Explicit:

VBA tries to execute and simply stops, giving that lovely run-time error: "Huh?"