Excel 2010 - ActiveX ComboBox Placed On All Worksheets With Dropdown Listing All Worksheets From Formula Generated List

[Mini_Mia]

Hi!

I haven't been able to find an answer on this and my issue could be piecing together code I found on multiple forums. This worked fine with the first combo box I tried but I am unable to get this to work on subsequent worksheets. My goal is to have an ActiveX ComboBox drop down worksheets navigator on each tab in the workbook.

In Lists!E:E I have the list of the actual worksheet names they can access dynamically populating based upon formulas and user selections. The number of items will shrink and grow to a currently unknown amount. (I did set the code for E2:E200 for my initial testing) This list is what I want to show as drop down selections.

The first/main worksheet is called Program_Variables with ComboBox21 and CommandButton21. After a worksheet name is selected in the drop down, they click the button to navigate to that worksheet. This functionality is what I would like to duplicate across all worksheets.

In case it is helpful the next tab I tried to place this on is called Summary-Program_Totals.

Here is the code that I was using, it worked successfully for the ComboBox on the Program_Variables tab but I can't seem to get this to duplicate or work when placed on the Summary-Program_Totals tab. I wasn't sure how to modify this properly.

VBA Code for the Program_Variables worksheet (ComboBox tab)

'******************************
'****WORKSHEET SELECTOR CODE****
'******************************

Private Sub ComboBox21_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
     If ComboBox21.Value = vbNullString Then Exit Sub
     If (KeyCode <> 13) Then Exit Sub
       Sheets(ComboBox21.Value).Select
End Sub

'************************************
'****WORKSHEET NAVIGATE BUTTON CODE****
'************************************

Private Sub CommandButton21_Click()
If ComboBox21.Value = vbNullString Then Exit Sub
    Sheets(ComboBox21.Value).Select
End Sub

VBA Code for the Lists worksheet

Private Sub Worksheet_Calculate()
    Dim cell As Range
    With Worksheets("Program_Variables").OLEObjects("ComboBox21").Object
    .Clear
    For Each cell In Range("E2:E200").SpecialCells(3)
    If Len(cell.Value) > 0 Then .AddItem cell
    Next
    End With
End Sub

 

[AlphaFrog]

You could populate the comboboxes by creating a dynamic named range of the sheets list (called SheetsList for this example)

SheetsList
Refers to: =OFFSET(Lists!$E$2,,,COUNTA(Lists!$E:$E)-1)

For each combobox, set the ListFillRange property to SheetsList. Then you wouldn't need any code to populate the comboboxes.

'******************************
'****WORKSHEET SELECTOR CODE****
'******************************

Private Sub [COLOR=#FF0000]ComboBox21[/COLOR]_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
     If [COLOR=#FF0000]ComboBox21[/COLOR].Value = vbNullString Then Exit Sub
     If (KeyCode <> 13) Then Exit Sub
       Sheets([COLOR=#FF0000]ComboBox21[/COLOR].Value).Select
End Sub

'************************************
'****WORKSHEET NAVIGATE BUTTON CODE****
'************************************

Private Sub [COLOR=#FF0000]CommandButton21[/COLOR]_Click()
If [COLOR=#FF0000]ComboBox21[/COLOR].Value = vbNullString Then Exit Sub
    Sheets([COLOR=#FF0000]ComboBox21[/COLOR].Value).Select
End Sub

For the two other procedures, change the names(red) to the button control and combobox control names for a given worksheet. They may or not be the same control names for each sheet depending how you added the controls.

It's not clear to me why you both a combobox keypress event procedure and a command button to do the same thing.

 

[Mini_Mia]

This is fantastic and A LOT more simple! Thank you so much!

Just to close out the thread -
"It's not clear to me why you both a combobox keypress event procedure and a command button to do the same thing."

I am new to VBA so I had used code from a couple different posts, one jumped sheets as soon as you selected the name in the dropdown. I wanted to modify that to not change until the button was pressed. I must have modified it incorrectly as I will admit I didn't really understand what it was doing. I didn't realize they were doing the same thing.

I used the SheetsList named range and updated my code to just use what is under "Worksheet Navigate Button Code". It is working perfectly!

 

[AlphaFrog]

You're welcome and thanks for the feedback.
Welcome to the forum.