Active X ComboBox in Worksheet

Question

I am using Active X ComboBox in worksheet in ComboBox1 i have listed the sheets name thats working fine.

I triying to add the data in ComboBox2 based on the sheet seleted in ComboBox1.

If i select the sheet name in ComboBox1 from dropdown list then automatically ComboBox2 needs to be fill the data from selected sheet and once i click the command button the sheet in ComboBox1 and details in ComboBox2 needs to select at the same time.Please help me on this topic it will more helpfull for me to develope my project.

Simply my qustion is i need to select the worksheet in ComboBox1 and need to select the ranges in ComboBox2 once i click the command button the specific sheet and ranges needs to select at a time.

Code below:

Private Sub CommandButton1_Click()
Dim sName As String
Dim x As Integer
Dim Range As Range
Dim Sh As Worksheet

   For x = 3 To Sheets.Count
       Sheets(x).Visible = False
   Next x

 sName = ComboBox1.Value

    With ActiveWorkbook.Sheets(sName)
  
        .Visible = True
        .Activate
    End With

End Sub

Private Sub Worksheet_Activate()

Dim Sh As Worksheet
Dim sName As String
Dim x As Integer

Me.ComboBox1.Clear
 For Each Sh In ThisWorkbook.Worksheets

    Me.ComboBox1.AddItem Sh.Name

Next Sh
Exit Sub

End Sub

Private Sub ComboBox2_Change()
Dim Sh As Worksheet
Dim sName As String

sName = ComboBox1.Value

If ComboBox1.Value Then

    Sheets(sName).Activate

    Set Sh = Worksheets("sName")

    If ActiveSheet.Name = Sh.Name Then

        ComboBox2.ListFillRange = "sName"

    Else

       Exit Sub

    End If

End Sub

Comments are not for extended discussion; this conversation has been moved to chat. — Samuel Liew

FaneDuru FaneDuru · Accepted Answer · 2020-09-18T20:19:16

Try the next approach, please:

Copy the next event code in the same sheet module. It will fill the ListBox1:

Private Sub Worksheet_Activate()
  Dim sh As Worksheet
  
  Application.EnableEvents = False
   ActiveSheet.OLEObjects("Listbox1").Object.Clear
   For Each sh In ThisWorkbook.Worksheets
     ActiveSheet.OLEObjects("Listbox1").Object.AddItem sh.Name
   Next
  Application.EnableEvents = True
End Sub

Copy the next code (ListBox1_Change event). It will fill the range of the ListBox2 for selected sheet in ListBox1:

Private Sub ListBox1_Change()
    Dim lst1 As MSForms.listBox, lst2 As MSForms.listBox, ws As Worksheet
    Dim lastRow As Long, arrNam As Variant, I As Long, k As Long
    
    Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
    Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object
    
    If lst1.ListIndex = -1 Then Exit Sub
    Set ws = Worksheets(lst1.Value)
    lastRow = ws.Range("A" & rows.count).End(xlUp).row
    If lastRow = 1 Then
        If ws.Range("A1").Value = "" Then
            MsgBox "Nothing on A:A column of sheet " & ws.Name: Exit Sub
        Else
            With lst2
                .Clear
                .ColumnCount = 2
                .AddItem ws.Range("A1").Value
                .list(0, 1) = 1
            End With
            Exit Sub
        End If
    End If
    ReDim arrNam(1 To 2, 1 To lastRow)
    For I = 1 To lastRow
        If ws.Range("A" & I).Value <> "" Then
            k = k + 1
            arrNam(1, k) = ws.Range("A" & I).Value
            arrNam(2, k) = I 'the row to be selected
        End If
    Next
    ReDim Preserve arrNam(1 To 2, 1 To k)
    With lst2
        .ListFillRange = ""
        .ColumnWidths = "100;0"
        .Clear
        .ColumnCount = 2 ' Worksheets(lst1.Value).Range(rngAddress).Columns.count
        .list = Application.Transpose(arrNam)
    End With
End Sub

Copy the next Sub code in the same sheet module. It will select all the ranges of listbox 2, from the sheet selected in listbox 1:

Sub SelectSheetNames()
  Dim lst1 As MSForms.listBox, lst2 As MSForms.listBox, i As Long
  Dim rngUnion As Range
    
    Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
    Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object
    Worksheets(lst1.Value).Activate
    For i = 0 To lst2.ListCount - 1
        If rngUnion Is Nothing Then
            Set rngUnion = Worksheets(lst1.Value).Range("A" & lst2.list(i, 1))
        Else
            Set rngUnion = Union(rngUnion, Worksheets(lst1.Value).Range("A" & lst2.list(i, 1)))
        End If
    Next i
    rngUnion.Select
End Sub

The above Sub can be called from a Button click event...

Reedited:

I think you will need the next listBox2 Change event. Please, copy the next code in the same sheet module:

Private Sub ListBox2_Change()
   Dim ws As Worksheet, lst1 As MSForms.listBox, lst2 As MSForms.listBox
   
   Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
   Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object
   Set ws = Worksheets(lst1.Value)
   If lst2.ListIndex = -1 Then Exit Sub
   MsgBox ws.Range("B" & lst2.list(lst2.ListIndex, 1))
End Sub

The above code will show in a MsgBox the content of the B:B column for the name you select in the listbox...

Please, test it and send some feedback.

Active X ComboBox in Worksheet

1 Answers