Comboboxes where in first one is list of the sheets and in second one is data from first column from selected sheet in first combobox

Question

I'm new to VBA and I have some problems with how to do everything automatically. I have an active sheet for analysis and 10 sheets with data. Due to the type of analysis, I would like to use comboboxes to select data for analysis.

I would like to:

1. To use combobox1 on activesheet to select sheet from where I read the data.
2. To use combobox2 where is a list of names from the first column from the sheet selected in combobox1.

Example: Sheets 2-10 are names of countries, Column 1 are cities in those countries with data related with each city. So in combobox1 I select UK =sheet(4) , and then in combobox2 London= cell A40. Then i calculate B40 x C40 /F40...

I started like:

Public Sub Worksheet_Activate()
    Dim x As Integer
    totalcountries = Sheets.Count
    For x = 2 To totalcountries
    Me.Combobox1.AddItem Sheets(x).Name
    Next x
End Sub

And:

Sub selectcity()
    Sheets(1).combobox2.List = Sheets(4).Range("A2:A56").Value 
End Sub

I don't know how to connect it. Thank you.

Answer 1

We could use the 'events' related to the comboboxes.

The method I have used here is:

(1 )When you click ComboBox1, it populates the names of all sheets except the ActiveSheet. You could now choose the sheet you desire from the dropdown.

(2 )When you TAB away from ComboBox1 (by pressing TAB), it populates ComboBox2 with the city names in the sheet you selected in ComboBox1.

I have named the ActiveSheet as "acSht" just for the purpose of explaining here. You could use your name. If you use a different name, replace "acSht" by the name you provide to the ActiveSheet.

Go to Developer and click 'Design Mode' under the 'Controls' section.

Now double-click ComboBox1. This will take you to Visual Basic Editor window. In the white pane that appears, You could see two dropdown boxes at the top. choose 'GotFocus' from the dropdown box on top right.

Use the following code.

Private Sub ComboBox1_GotFocus()

Worksheets("acSht").ComboBox1.Clear

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    
        If Not ws.Name = "acSht" Then ThisWorkbook.Worksheets("acSht").ComboBox1.AddItem ws.Name
        
    Next ws
    
End Sub

Next choose 'KeyDown' from the dropdown box on top right and use the following code.

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Worksheets("acSht").ComboBox2.Activate

If KeyCode = vbKeyTab Then
    Worksheets("acSht").ComboBox2.Clear

        Dim ws As Worksheet, rng As Range, wsName As String
        
        If Not ThisWorkbook.Worksheets("acSht").ComboBox1.Value = Empty Then
            wsName = ThisWorkbook.Worksheets("acSht").ComboBox1.Value
            Set ws = ThisWorkbook.Worksheets(wsName)
        Else
            Exit Sub
        End If
        
            For Each rng In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
            
                ThisWorkbook.Worksheets("acSht").ComboBox2.AddItem rng.Value
            
            Next rng
End If

End Sub