ComboBox default value on Workbook_Open()

[simurq]

This is the code (object module) stored under Sheet1:

Public RegNo As Range

Public Sub ComboBox1_DropButt*******()
    Dim xCols As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Temp As String
    Dim title As String
    Dim arrStr1() As Variant
    Dim arrStr2() As Variant
        
    Set RegNo = Range("$X$4")
    xCols = Sheet2.Cells(2, Columns.Count).End(xlToLeft).Column
    ReDim Preserve arrStr1(xCols - 2)
    ReDim Preserve arrStr2(xCols - 2)
    
    With Sheet2
        If ComboBox1.ListCount > xCols - 2 Then
             ComboBox1.Clear
        End If
        
    'Concatenate and remove double space in object names
        For i = 2 To xCols
            Temp = .Cells(2, i).Value & " " & .Cells(3, i).Value & " " & .Cells(4, i).Value
            title = Replace(Temp, "  ", " ")
            arrStr1(i - 2) = title
        Next i
    End With
    
    'Assign values of array 1 to array 2
    'to prepare for comparison of arrays
    For i = LBound(arrStr1) To UBound(arrStr1)
        arrStr2(i) = arrStr1(i)
    Next i
    
    'Sort array items alphanumerically
    For i = LBound(arrStr2) To UBound(arrStr2) - 1
        For j = i + 1 To UBound(arrStr2)
            If arrStr2(i) > arrStr2(j) Then
                Temp = arrStr2(j)
                arrStr2(j) = arrStr2(i)
                arrStr2(i) = Temp
            End If
        Next j
    Next i
    
    'Populate combo-box with sorted array items
    For i = LBound(arrStr2) To UBound(arrStr2)
        ComboBox1.AddItem arrStr2(i)
    Next i
    
    'Compare arrays and find corresponding column number on Sheet2
    For i = LBound(arrStr2) To UBound(arrStr2)
        If ComboBox1.Value = arrStr1(i) Then
            RegNo.Value = Sheet2.Cells(5, i + 2)
        ElseIf ComboBox1.Value = "" Then
            RegNo.Value = ""
        End If
    Next i
End Sub

And this is the other one stored under "ThisWorkbook" and calling the first procedure upon opening the workbook. What I try to achieve here is to set default value to combobox when the workbook opens:

Private Sub Workbook_Open()
    Sheet1.ComboBox1_DropButt*******
    Sheet1.ComboBox1.ListIndex = 0
End Sub

Both modules work OK to some extent but I have two problems:

1) Upon opening the workbook a value of ComboBox1 (ListIndex = 0) must be sought for in Sheet2 and the found value assigned to Range("X4"). This works OK when I drop down the combobox menu (since it's linked to "DropButt*******" event ) but NOT on Workbook_Open() although it calls the same ComboBox1 on run-time. How can I achieve the same functionality in both procedures?

2) When I use the first code ONLY, I can select combo box items from the menu. When I use both - I cannot! Is it somehow related to "ComboBox1.Clear" method?

Thank you very much!!!

 

[simurq]

BUMP!

Any help is highly appreciated!!!