Excel-form-controls-aaaargh!

Excel 2007.

I worked out how to put form-controls on a spreadsheet. Combo Boxes.

(Ain't the ribbon lovely? You're looking for the (hidden, IIRC) Developer ribbon....)

I worked out how to put some options in the Combobox (henceforth ddlb) using a range as the source.

I worked out how to attach a macro to execute on-change. Then in the code, I want to get the text in the box.

Minutes passed as I failed to work it out using autocomplete. I googled. Hours passed as I continued to fail.

Can it really possibly be as convoluted as I've made it?

Anyway, I bring you (me) a solution using the now-hidden / obsolete DropDowns property...

Function GetDDLBText(controlname As String) As String

Dim wsheet As Worksheet

Set wsheet = ThisWorkbook.Worksheets("")

Set allDropDowns = wsheet.DropDowns

Dim ddlb As DropDown

Set ddlb = allDropDowns(controlname)

If ddlb Is Nothing Then

GetDDLBText = ""

MsgBox ("Trapped error - control named '" & controlname & "' is not found.")

Exit Function

End If

GetDDLBText = "" ' default to this.

If ddlb.ListIndex > 0 Then

GetDDLBText = ddlb.List(ddlb.ListIndex)

End If

Exit Function

End Function