How to initialize a string with varying VBA Combo Box names

Question

Working in Excel, I have 10 worksheets and each of them has the same ActiveX Combo Box. The Combo Box lists a subset of other worksheets within the workbook.

Once a worksheet is selected in the Combo Box, one can then click a button next to it that runs a macro that fills in data from the selected sheet onto the active sheet.

My issue is that there is currently a single module that initializes the worksheet name by refering to the Combo Box name, which is different on every sheet (i.e. ComboBox1 to Combox10).

What I'm doing right now is changing the Combo Box reference each time I run the macro on each of the 10 sheets that has this Combo Box. Unfortunately, I can't expect my users to update the macro each time they use it.

Is there a way to edit this Combo Box reference in the module using a wildcard, or is there a different way to go about this issue?

I've tried adding the public sub into the private sub for each worksheet, but then I come across the 400 error. I've also tried throwing an * after ComboBox, (i.e. ComboBox*), but that clearly isn't correct either.

Sub FillTable()

Dim lastrow, lastrow2, i As Integer
Dim Searchfor, j, candnumArr, MarksArr As Variant
Dim wsName As String

    'Worksheet selected in Combobox which differs across worksheets
    ' This is the reference that needs to be changed from 1 to 2/3/etc
    wsName = ActiveSheet.ComboBox1.Text

    With Sheets(wsName)
        lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        candnumArr = Range(.Cells(6, 2), .Cells(lastrow, 14))
    End With

    'Arrays to be filled sheet
    With ActiveSheet
        lastrow2 = .Cells(Rows.Count, "H").End(xlUp).Row
        ' load variant array with search variables
        searcharr = Range(.Cells(6, 8), .Cells(lastrow2, 8))
        ' define an output aray
        PartNumArr = Range(.Cells(6, 13), .Cells(lastrow2, 13))
        MarksArr = Range(.Cells(6, 17), .Cells(lastrow2, 26))
    End With

    On Error Resume Next

    For i = 1 To lastrow2

        For j = 1 To lastrow
            Searchfor = searcharr(i, 1)
                If candnumArr(j, 1) = Searchfor Then
                    For kk = 1 To 13
                        PartNumArr(i, kk - 1) = candnumArr(j, kk)
                        MarksArr(i, kk - 2) = candnumArr(j, kk)
                    Next kk
                    Exit For
                End If

        Next j

    Next i

    ' writeout the output arrays
    With ActiveSheet
            Range(.Cells(6, 13), .Cells(lastrow2, 13)) = PartNumArr
            Range(.Cells(6, 17), .Cells(lastrow2, 26)) = MarksArr
    End With

End Sub

While the code works for the first sheet and using its Combo Box, I'm at a loss at how to change it so it works across all 10 sheets that have this Combo Box.

Unrelated: Delete On Error Resume Next, it only hides Errors: rubberduckvba.wordpress.com/2019/05/22/on-error-resume-next — L8n
If it is a ListBox you can Reference the DropDowns Collection of the worksheet and (as long as there is only one Combox per Sheet) use worksheet.DropDowns(1) — L8n
@L8n I'm trying to change the wsName = ActiveSheet.ComboBox1.Text line to change to something like wsName = ActiveSheet("ComboBox"&"#").Text so that the reference is dynamic. That specific line doesn't work, but is something is there a way to make something else work? — gbear84

L8n L8n · Accepted Answer · 2019-07-10T07:39:49

The name of an ActiveX element can be the same across multiple Worksheets, so you could just name them all the same.

If it is an ActiveX Control you can use one of these 3 Method (there even more):

Sub testBox()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        'If they have the same name
        Debug.Print ws.OLEObjects("ComboBox1").Object.Value

        'If you only have one ActiveX object per Page
        Debug.Print ws.OLEObjects(1).Object.Value

        'If you want to loop it and check by name....
        Dim cb As OLEObject
        For Each cb In ws.OLEObjects
            If Left(cb.Name, 8) = "ComboBox" Then Debug.Print cb.Object.Value
        Next cb

    Next ws
End Sub

The example is more or less taken directly from the Official Microsoft Documentation! https://docs.microsoft.com/en-us/office/vba/excel/concepts/controls-dialogboxes-forms/using-activex-controls-on-sheets

How to initialize a string with varying VBA Combo Box names

1 Answers