Excel for macOS - access ComboBox from VBA

Question

I am facing issue with accessing value of ComboBox. I have assigned macro to it, but I cannot find out, what name it has assigned. Because of "object properties" is missing in macOS Excel VBA, I have no idea how to resolve this. My Excel is in Slovak Language (therefore suggested method for macro was Rozbaliť1_Zmena), but when I am accessing it through "Rozbaliť1" , code has problem with diacritics in the name of object.

Sub ComboBox_Change()

    Dim strSelectedItem As Variant
    strSelectedItem = ActiveWorkbook.Worksheets(1).???.Value
    MsgBox strSelectedItem

End Sub

It is not working even with english version: strSelectedItem = Sheets("REPORT").DropDown1.Value — Martin Fedy Fedorko, Oct 07 '20 at 09:09
Resoltion is strSelectedItem = Sheets("REPORT").DropDowns("Drop Down 1").Value — Martin Fedy Fedorko, Oct 07 '20 at 09:23

score 0 · Answer 1 · answered Oct 07 '20 at 09:25

Name can be changed in the left corner of the table (where "Range" is displayed). Regarding code, I have found resolution to this issue:

Sub DropDown1_Change()
    Dim strSelectedItem As Variant
    Dim dd As DropDown

    Set dd = ActiveSheet.DropDowns("Drop Down 1")
    Set MyRange = Sheets("HELP").Range(dd.ListFillRange)
    
    strSelectedItem = MyRange(dd.Value)
    
    MsgBox strSelectedItem
    
End Sub

More info: Get dropdown value in VBA and get the name of the dropdown...nowhere to be found?

Excel for macOS - access ComboBox from VBA

1 Answers1