Dialog Sheet in Excel returns error when I try to save value of combobox on sheet

Question

I have a DialogSheet (not a userform, this is just the way the sheet was set up nearly 20 years ago) that I have added a combobox to. I am trying to save the value of that combobox to a cell on my sheet but it I get the following error:

Run-time error '438' : Object doesn't support this property or method.

Here is the sub that causes the error:

Sub ModelNameSelection_Change()

Dim ModelName As String

     ModelName = DialogSheets("setup").ModelNameSelection.Value

     Worksheets("sheet1").Unprotect

     Worksheets("sheet1").Cells(1, 10).Value = ModelName

     Worksheets("sheet1").Protect 

End Sub

The dialog sheet the combobox is on is called "setup" and the combobox has the name ModelNameSelection.

Is there a specific way to refer to the value in the drop down box?

Answer 1

Replace your line:

ModelName = DialogSheets("setup").ModelNameSelection.Value

with:

ModelName = DialogSheets("setup").DropDowns("ModelNameSelection").Value

EDIT: Oops I didn't see @Tim Williams comment which has the same answer

Answer 2

From this post - this should get you started:

Dim sheet As Object ' As DialogSheet?
Set sheet = DialogSheets("setup")
Dim shp As Excel.Shape
For Each shp In sheet.Shapes
    If shp.Type = msoFormControl Then
        If shp.FormControlType = XlFormControl.xlDropDown Then
            'todo
        End If
    End If
Next

In other words, you access controls through the sheet's Shapes collection. Since you know the exact name of the control, you can retrieve the shape by name instead of iterating them all:

Set shp = sheet.Shapes("ModelNameSelection")
Debug.Assert shp.FormControlType = XlFormControl.xlDropDown

And from there use its ControlFormat property to get the control's Value:

ModelName = shp.ControlFormat.Value