Excel VBA for Mac: ComboBox Workaround

0
votes

I am unable to populate my ComboBox on a UserForm on a Mac.

I have created a dynamic named range called "nameList", whose formula looks as such:

"=OFFSET('MEMBERS LIST'!$D$4,0,0,COUNTA('MEMBERS LIST'!$D:$D)-1)"

On Sheet1 (named: HOME), I have created a Macro which calls up a UserForm (named: PostForm), where I have created a ComboBox (named: username_Insert). On Windows, I simply have to go to the Properties box & type "nameList" in the RowSource property. This populates the ComboBox with my dynamic nameList. However, when I attempt to read my Worksheet on a Mac, the list turns up empty.

I have tried multiple solutions within code:

Private Sub PostForm_Initialize()
    'Populate Username combo box.
    'Me.username_Insert.List = [nameList]
    'Me.username_Insert.List = Range("nameList")
    'Me.username_Insert.List = Range([nameList])

    'Me.username_Insert.AddItem = [nameList]
End Sub

I'm unsure if I'm entering the code in the correct sub or if I should be entering my code within the following sub: Private Sub username_Insert_Change()

/*******RESOLVED*******/

Modified the code to take in the other dynamic lists I had, resulted in the following code. Implemented into the UserForm Code, worked like a charm:

Private Sub UserForm_Activate()
    Dim nme As Range
    Dim ws As Worksheet

    Set ws = Sheets("Members List")
    For Each nme In ws.Range("nameList")
        Me.username_Insert.AddItem nme.Value
    Next nme

    Set ws = Sheets("SONY DATA")
    For Each nme In ws.Range("phoneList")
        Me.phone_Insert.AddItem nme.Value
    Next nme

    For Each nme In ws.Range("ampm")
        Me.daynight_Insert.AddItem nme.Value
    Next nme

    For Each nme In ws.Range("typeList")
        Me.type_Insert.AddItem nme.Value
    Next nme

End Sub
2
excelmacosvbacombobox

2 Answers

0
votes

Looks like you'll have to populate your combobox item-by-item. Given that it is a dynamic list I suggest that you put the code in the Userform's Activate event. Note that this event is found (and the code should be placed) in the Userform's code module NOT in a general code module.

Private Sub UserForm_Activate()
Dim nme As Range
Dim ws As Worksheet

Set ws = Sheets("Members List")
    For Each nme In ws.Range("nameList")
        Me.username_Insert.AddItem nme.Value
    Next nme

End Sub
0
votes

Adding items line-by-line works, but it can be done more straightforwardly (and elegantly) by simply populating the LIST property of the Combobox with all the values in one line of code as follows :

Private Sub Userform_Activate()
    username_Insert.List = Sheets("Members List").Range("nameList").value
End sub

You were very close in your original attempts, you just needed to add .value to the

=Range("nameList")

line. :-)