Using Excel VBA commands with variables

Question

I'm trying reuse some code and thus make controlls in call a function to get different results. The problem seem to be to get references to the different controlls I want to update.

Function populate(num1, num2)

Dim index As Integer
index = ComboBox1.ListIndex

ComboBox2.Clear

Select Case index
    Case Is = 0
        With ComboBox2
            ComboBox2.List = Worksheets("Sheet1").Range("A1:A10").Value
        End With
    Case Is = 1
        With ComboBox2
            ComboBox2.List = Worksheets("Sheet2").Range("A1:A10").Value
        End With
    Case Is = 2
        With ComboBox2
            ComboBox2.List = Worksheets("Sheet3").Range("A1:A10").Value
        End With
End Select

End Function

Private Sub ComboBox1_Change()
Call populate(1, 2)
End Sub

Private Sub ComboBox3_Change()
Call populate(3, 4)
End Sub

Private Sub ComboBox5_Change()
Call populate(5, 6)
End Sub

Naturally, for every combobox I call ( odd numbers ), it should update the values of the 2nd combobox ( even numbers ). But instead of copying the code to each and every odd numbered combobox, I d like to have some sort of code that actually gets reused by function calls as shown above.

Unfortunatly, I'm unsure of how to make that work in VBA. Needless to say, I'd like it to be something like ( which does not work ) :

Function populate(num1, num2)

Dim index As Integer
index = ComboBox + num1 + .ListIndex

ComboBox + num1 + .Clear

Select Case index
    Case Is = 0
        With ComboBox + num2
            ComboBox + num2 + .List = Worksheets("Sheet1").Range("A1:A10").Value
        End With
    Case Is = 1
        With ComboBox + num2
            ComboBox + num2 + .List = Worksheets("Sheet2").Range("A1:A10").Value
        End With
    Case Is = 2
        With ComboBox + num2
            ComboBox + num2 + .List = Worksheets("Sheet3").Range("A1:A10").Value
        End With
End Select

End Function

Also had a look at the possible duplicated question, tough that did not solve my problem. In fact, even if the subject seems dupicate, I don't think the question is. I'm trying to combine a partial command and a variable in order to create the command I want to use, whilst the possible duplicate seems to want something slightly different ... I might be wrong, but that's what it seemed like to me.

Possible duplicate of Referencing Excel Userform Control Name Value From String (VBA) — GSerg
– GSerg, Commented Sep 29, 2016 at 19:58

YowE3K · Accepted Answer · 2016-09-29 20:23:21Z

1

Instead of passing a number to your populate subroutine, pass the ComboBoxes that you wish to process:

Sub populate(cb1 As ComboBox, cb2 As ComboBox)
    Dim index As Integer
    index = cb1.ListIndex

    With cb2
        .Clear

        Select Case index
            Case 0
                .List = Worksheets("Sheet1").Range("A1:A10").Value
            Case 1
                .List = Worksheets("Sheet2").Range("A1:A10").Value
            Case 2
                .List = Worksheets("Sheet3").Range("A1:A10").Value
        End Select
    End With
End Sub

Private Sub ComboBox1_Change()
    populate ComboBox1, ComboBox2
End Sub

Private Sub ComboBox3_Change()
    populate ComboBox3, ComboBox4
End Sub

Private Sub ComboBox5_Change()
    populate ComboBox5, ComboBox6
End Sub

answered Sep 29, 2016 at 20:23

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Werrf · Accepted Answer · 2016-09-29 20:12:24Z

1

You can reference your comboboxes and other controls using the Sheet.Drawingobjects collection, as:

ActiveSheet.DrawingObjects("ComboBox" & num2)

answered Sep 29, 2016 at 20:12

Werrf

1,2447 silver badges17 bronze badges

Comments

user3598756 · Accepted Answer · 2016-09-29 20:22:08Z

1

Assuming you have userform comboboxes then your code can shorten down to:

Option Explicit

Sub populate(num1, num2)
    Dim shtName As String

    shtName = "Sheet" & (Me.Controls("Combobox" & num1).ListIndex + 1)
    With Me.Controls("Combobox" & num2)
        .Clear
        .List = Worksheets(shtName).Range("A1:A10").Value
    End With
End Sub

Otherwise, if you have ActiveX Excel comboboxes then it slightly changes to:

Option Explicit

Sub populate(num1, num2)
    Dim shtName As String

    shtName = "Sheet" & (ActiveSheet.DrawingObjects("ComboBox" & num1).Object.ListIndex + 1)
    With ActiveSheet.DrawingObjects("Combobox" & num2).Object
        .Clear
        .List = Worksheets(shtName).Range("A1:A10").Value
    End With
End Sub

edited Sep 29, 2016 at 20:22

answered Sep 29, 2016 at 20:12

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

3 Comments

SkyRaider Over a year ago

Not useing userforms, the activeX controlls are right there on the workbook. My father-in-law is trying to use it as a document of sorts.

SkyRaider Over a year ago

hah beat me to it with an edit :) Interesting. I'll check it out tomorrow

user3598756 Over a year ago

@user2546442, see edited answer for ActiveX Excel comboboxes. If I fulfilled your question please mark answer as accepted. thank you

Collectives™ on Stack Overflow

Using Excel VBA commands with variables

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related