Assign Macro to drop down box 2

[LydiaC]

Excel 2000-How can I assign a macro to a drop down box? Can I program my workbook to compare 3 drop down boxes and then run the correct macro?

Thanks in advance!!

 

[xlbo]

You can assign a macro to a number of events associated with a combobox - right click on the combobox and choose "View Code"

Use the drop down box on the right to view what events you can attach code to

As for the 2nd part of the question, the answer is almost certainly yes but a bit more explanation would be useful Rgds
~Geoff~

 

[LydiaC]

Thanks for your help! More info?...I would have two drop down boxes. Between the two boxes they offer 8 different combinations. Right now I have 8 buttons each assigned to a different macro which will send the user to the appropriate page (i.e. if they choose "User" and "Region 1" it would send them to the "Region 1-User" sheet to view their information. My client wants drop down boxes and be able to choose Users vs Non-Users, and which region they want to view. How can I write a program to compare the drop down boxes and then run the appropriate macro?

 

[rmikesmith]

LydiaC,

I have put together a demo workbook that achieves what you are asking for. If you would like a copy post your email. I am also describing it here for the benefit of others.

The workbook has a main worksheet containing two dropdown listboxes (ComboBox controls), one for user status (User/Non-User) and the other for Region (I used the descriptions Region1, Region2 & Region3). The dropdowns are filled from worksheet cells (using their ListFillRange properties). I used columns A & B but you would want to put these out of the way or perhaps hide the relevant columns. There are 6 other worksheet named according to the combinations of user status and region (e.g. Region1 User, Region2 User, ... Region3 Non-User).

I called a single procedure from each dropdown's Change event procedure (see Geoff's instructions). The code for this looks like:

Private Sub ComboBox1_Change()
  If ActiveX_Event_Disabled Then Exit Sub
  ActivateSheet
End Sub

Private Sub ComboBox2_Change()
  If ActiveX_Event_Disabled Then Exit Sub
  ActivateSheet
End Sub

I created the ActivateSheet procedure in a standard code module:

Public ActiveX_Event_Disabled As Boolean


Sub ActivateSheet()
Dim SheetName As String

  On Error Resume Next

  With Worksheets("Main")
    If (.OLEObjects("ComboBox1").Object.ListIndex = -1) Or (.OLEObjects("ComboBox2").Object.ListIndex = -1) Then
      Exit Sub
    End If
    
    With .OLEObjects("ComboBox2").Object
      SheetName = .List(.ListIndex)
    End With
    With .OLEObjects("ComboBox1").Object
      SheetName = SheetName & " " & .List(.ListIndex)
    End With
    
    ActiveX_Event_Disabled = True
    .OLEObjects("ComboBox1").Object.ListIndex = -1
    .OLEObjects("ComboBox2").Object.ListIndex = -1
    ActiveX_Event_Disabled = False
  End With
  
  Worksheets(SheetName).Activate

End Sub

A comment about the variable

ActiveX_Event_Disabled

: I discovered that when my code cleared the dropdowns it fired their Change events. Apparently, ActiveX controls do not obey the

Application.EnableEvents = False

property setting. So, I used a boolean flag to indicate that the Change event procedure should exit. Anyone know of another way to handle this?

Regards,
Mike

 

[xlbo]

Seems like Mike's got this one covered Rgds
~Geoff~

 

[LydiaC]

Thank you, thank you, thank you...to both Mike and Geoff! Here is my email: lcoonen@ameritech.net
Thanks,
Linda

 

[xlbo]

Star for Mike being as he provided all the code ;-) Rgds
~Geoff~