Excel Activex combobox activate when excel workbook is opened

Question

I have a few activex combobox and vb code in excel sheet 1. But everytime after I open the workbook, I need to open the code window and run the code to activate the combobox. Is there any way that I can automatically activate and run the code in sheet 1, belonging to combobox, once I open the workbook?

I tried looking into other forums/questions but could not find any solution. sheet1.combobox1.activate in thisworkbook code is not working either. T

Following is the code that is in sheet 1 that needs to be activated.

Public oDictionary As Object

Private Sub ComboBox1_Click()
    Dim r As Range
    Dim list As Object

    Set oDictionary = CreateObject("Scripting.Dictionary")

    With Sheet2
        For Each r In .Range("C11", .Cells(.Rows.Count, "c").End(xlUp))  

            If Not oDictionary.Exists(r.Text) Then                       
                Set list = CreateObject("System.Collections.ArrayList")  

                oDictionary.Add r.Text, list
            End If

            If Not oDictionary(r.Text).Contains(r.Offset(0, 1).Value) Then  
                oDictionary(r.Text).Add r.Offset(0, 1).Value                
            End If

        Next
    End With

    ComboBox1.list = oDictionary.Keys  'Display the list in combobox 1
End Sub

oxwilder oxwilder · Accepted Answer · 2018-10-11T00:40:12

You can run code on workbook open automatically with sub workbook_open() and then create the combobox on that event as opposed to the click event:

Dim r As Range
Dim list As Object
Dim rng As Range
Dim rng1 As Range


Set oDictionary = CreateObject("Scripting.Dictionary")

With Sheet2
    Set rng1 = .Range("C11", .Cells(.Rows.Count, "c").End(xlUp))
    For Each r In rng1.Cells

        If Not oDictionary.Exists(r.Text) Then
            Set list = CreateObject("System.Collections.ArrayList")

            oDictionary.Add r.Text, list
        End If

        If Not oDictionary(r.Text).Contains(r.Offset(0, 1).Value) Then
            oDictionary(r.Text).Add r.Offset(0, 1).Value
        End If

    Next
End With

Set rng = Worksheets(1).Range("c11") 'where the dropdown list will go
    With rng
        Set box1 = Worksheets(1).DropDowns.Add(.Left, .Top, .Width, .Height)
            With box1
              .Name = "ComboBoxIn" & rng.Address(False, False)
              .list = oDictionary.Keys
            End With
     End With
end sub

Excel Activex combobox activate when excel workbook is opened

2 Answers