VB code help

[Anthony]

I have a combobox that displays a list of 'options' to the user.
I want a certain macro to run or userform to be displayed for each 'option'
that is chosen.
I have this code, but I can't get it to work

Sub DropDown49_Change()
Select Case DropDown49.Value

Case "Add new Job"
frmNewJob.show
Case "Edit report"
frmEditJobref.show
Case "View old report"
frmGetJob.show
Case "View Adhoc Log"
Sheets("adhoc").Select
Case "Add Shift Report"
Sheets("sheet2").Select
frmReport.show

End Select

any help in fixing it and where to place the code would be appreciated

 

[Joel]

the combobox change functioln need to be in a VBA sheet page corresponding to
the same sheet the combobox is located. this is what I usually do. Read the
entire instructions before you make any changes.

1) Worksheet menu - view - Toolsbars - Control Toolbox
2) Add combobox
3) Right click combox and select veiw code.
4) Now change combox name if necessary
Right click combobox and select properties. change Name of combobox
and macro to the same name. The drop down change will not work if they are
different.
5) Exit Design mode. The Design Mode ICON is the triangle on the Control
Toolbox Toolbar. This is a toggle button which either Enters/Exit Design
Mode. You can only see the properties when in Design Mode. The drop down
change will only work if you exit design mode.

 

[Dave Peterson]

This looks like you're using a DropDown from the Forms toolbar.

You could replace it (like Joel suggested) or you could modify your code.

The .value of a dropdown isn't text. It's a number.

The first item is 1.
The 2nd is 2.
The 3rd is 3.
and so forth

So you could change your code (assuming I got the values in the right order):

Sub DropDown49_Change()
Select Case DropDown49.Value
Case 1
frmNewJob.show
Case 2
frmEditJobref.show
Case 3
frmGetJob.show
Case 4
Sheets("adhoc").Select
Case 5
Sheets("sheet2").Select
frmReport.show
End Select
End Sub

Or you could use the .list property:

Sub DropDown49_Change()
Select Case DropDown49.list(dropdown49.listindex)

Case "Add new Job"
frmNewJob.show
Case "Edit report"
frmEditJobref.show
Case "View old report"
frmGetJob.show
Case "View Adhoc Log"
Sheets("adhoc").Select
Case "Add Shift Report"
Sheets("sheet2").Select
frmReport.show
End Select
end sub