CODEWITHSUNDEEP
excelvbacombobox
DevilWAH
DevilWAH

Reputation: 2643

listfillrange excel

I know how to assign the values to a combo box with code, but what I want to do is say the list is from 0-20

in fact I have a number of combo boxes each with different ranges. Now I know I could create arrays on the fly to suit and assign these, but is there any way of populating a combo list by giving a min and max value and with out using multiply arrays?

so
combobox_1 = 1 to 67
combobox_2 = 5 to 20
combobox_3 = 23 to 33
...
...
combobox_87 = 84 to 108

what's the tidiest way to code it?

Regards

Aaron

Upvotes: 0

Views: 356

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

I agree with @Tim Williams. Just put it out in a function like this

Sub Fillcb()

    Sheet1.ComboBox1.List = ConsecutiveArray(5, 20)

End Sub

Function ConsecutiveArray(lMin As Long, lMax As Long) As Variant

    Dim aReturn() As Long
    Dim i As Long
    Dim lCnt As Long

    ReDim aReturn(0 To lMax - lMin)

    For i = lMin To lMax
        aReturn(lCnt) = i
        lCnt = lCnt + 1
    Next i

    ConsecutiveArray = aReturn

End Function

Upvotes: 1

Related Questions