CODEWITHSUNDEEP
excelvba
Sudio
Sudio

Reputation: 155

Can't get text value from dropdown list

Building off an earlier question...I've built a dropdown that successfully adds table headers to the list, and selects the first item. I need to use the text value in the drop to change formulas, but the dropdown always seems to have a value of 0.

'earlier in a separate subroutine
Dim combo As Object
Set combo = ThisWorkbook.Sheets("AdvStats").Shapes.AddFormControl(xlDropDown, _
                                          Left:=Cells(1, tCols + 2).Left, _
                                          Top:=Cells(1, tCols + 2), _
                                          Width:=200, _
                                          Height:=Cells(1, tCols + 2).Height)
'some code to populate dropdown
combo.ControlFormat.ListIndex = 1 'this works, thanks @SJR
combo.name = "top25Select"
combo.OnAction = "top25Select_Change"
' end earlier code

Sub top25Select_Change()

    Dim tbl As ListObject: Set tbl = Sheets("AdvStats").ListObjects("AdvStatsTable")

    With tbl.DataBodyRange
        tRows = .Rows.Count
        tCols = .Columns.Count
    End With

    Dim combo As Object
    Set combo = ThisWorkbook.Sheets("AdvStats").Shapes("top25Select")

    Range(Cells(3, tCols + 2), Cells(27, tCols + 2)).Formula = "=Row() - 2"
    'Range(Cells(3, tCols + 3), Cells(27, tCols + 3)).Formula = "=" & howeverIReferenceTheValue & "" 
'this is where i'd like to use the dropdown text value, but combo.controlformat.value is always 0, and list property isn't available

End Sub

The dropdown change does trigger top25Select_Change(), just stuck on getting the text value. Not sure what I'm doing wrong here. Any suggestions?

Edit: I've used this as a workaround:

Dim message As Variant
message = combo.ControlFormat.Value

Then I've got an integer which I can use to reference the cell where I pull it from. It works for my needs, but not sure if it's the best solution.

Upvotes: 0

Views: 130

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

The activeX combo value can be returned using:

"Your text" =Worksheets("AdvStats").OLEObjects("top25Select").Object.Value

Upvotes: 1

Related Questions