Run a macro from drop down list choice

[netserf]

I am almost too embarrassed to ask my question. If it weren't for the fact I NEED to solve this issue I would just drop the whole thing...BUT, I need a solution. I have looked at all the posts (most anyway) relating to how one fires a macro upon a dropdown choice. Almost all refer to the change value function. I have placed a (forms) dropdown menu on sheet 1 and the list items a located on sheet 2. The cell holding the value is located on sheet 1 in cell H33. The dropdown list contains list item "Purchase", "Refinance", "Streamline". Cell H33 reflect "1" when "Purchase" is selected and "2" when "Refinance" and so on. I have a macro named Purchase which runs with no errors when tested by itself. However, when I try to tie the drop down list choice to the macro NOTHING happens. No error message box-nothing. For my life I cannot seem to get this basic function. I have tried putting the code on the sheet, in separate modules-nothing seems to be working. Can someone please show me the way on this.

 

[BrianExcel]

Have you tried "Select Case"?

dim sValue as String

sValue= Userform1.combobox1

Select Case sValue
Case "1"
Insert code here
Case "2"
Insert code here
End Select

You will need to replace some of the values here, like case values, userform and combobox names, etc. You can also use this if your drop downs are on a sheet, you just have to refer to them that way.

Let me know if you have questions.

 

[Tom Urtis]

Just to be clear, it it your intention or wish to have "Purchase" or "Refinance" or "Streamline" be shown in cell H33 or just a 1 or 2 or 3. In any case, where are those numbers generated from in the first place and what is their significance with what you are doing?

 

[netserf]

Have you tried "Select Case"?

dim sValue as String

sValue= Userform1.combobox1

Select Case sValue
Case "1"
Insert code here
Case "2"
Insert code here
End Select

You will need to replace some of the values here, like case values, userform and combobox names, etc. You can also use this if your drop downs are on a sheet, you just have to refer to them that way.

Let me know if you have questions.

I have tried select case to no avail. I am not using a Userform. I just selected the dropdown form from the insert form objects. The form automatically assigns a number to the list items, ie list item one (Purchase) is assigned the number 1 and so on. If I right click on the drop down object it is named "drop down 55" in the cell reference box. Sorry for my shortcoming here....so with the code above, how would I refer to the drop down object if it is not on a user form?

 

[BrianExcel]

You should be able to change the reference from userform1 to sheet1 (or whatever number the sheet is).

 

[BrianExcel]

Also, if you are using a drop down that is not data validation-based on a sheet, you should be using an ActiveX control, NOT a forms control. They are both in the same place, but there are two panes of controls, one for forms and one for ActiveX. make sure you are using the right one.

 

[BrianExcel]

Try this...

dim sValue as String

sValue = Sheet1.ComboBox1.value

Select Case sValue
    Case "Purchase"
       Call Purchase
    Case "Refinance"
       Call Refinance"
    Case "Streamline"
       Call Streamline
End Select

 

[BrianExcel]

The code I posted above works fine when using an ActiveX control. It did not work when I used a forms control.

 

[netserf]

OMG! Thank you, thank you BrianExcel. That was it. Active X control. BTW. When would one use the forms control as opposed to active x control?

 

[BrianExcel]

If you are developing using userforms, that's when you would use the forms controls. I don't know why they have them listed within the main Excel window since you have to build forms within the VBA editor, but now you know. Just use ActiveX controls if you are working within the worksheets themselves.

Glad to be of help.