Error with user form control combo box.

[mthomas57]

This is Mike once again. Trying to read the value from a user form control combo box. Can anyone explain why I'm getting this error from the code below? The error occurs at "LetterID = .List(.ListIndex)". LetterID is a variant type.
Thanks in advance!

Error: Object doesn't support this property or method


Code:

Dim wk As Worksheet
Dim dd As Shape

Set wk = ThisWorkbook.Worksheets(WSName)
Set dd = wk.Shapes("Cmb_LetterID")

With dd.ControlFormat
LetterID = .List(.ListIndex)
End With

 

[Norie]

What do you mean by a 'user form control' combo box?

If this control is on a sheet it's either a Forms control or an ActiveX control, if it's the latter it's similar to a userform control but it's not the same.

 

[DanteAmor]

Your code is correct if you have a form control combo on the sheet.
Apparently you have an ActiveX control combo, then the code should be:

    With Sheets(wsname).Cmb_LetterID
        LetterID = .List(.ListIndex)
    End With

 

[mthomas57]

Thanks so much for the reply. This is a Form Control drop down. There is an activeX on the same sheet because I'm trying to get one of them to work and neither one does. I'm focusing now on the Form Control to get it to work. Just cant read the value from the drop down. It gives me the error "Object doesn't support this property or method" at line LetterID = .List(.ListIndex).

Thanks again for your help
Mike

 

[DanteAmor]

I did the test with both codes and it works

Sub test1()
'Form Control
    wsname = "Hoja9"
    Dim wk As Worksheet
    Dim dd As Shape
    
    Set wk = ThisWorkbook.Worksheets(wsname)
    Set dd = wk.Shapes("Cmb_LetterID")
    
    With dd.ControlFormat
        LetterID = .List(.ListIndex)
    End With
End Sub

'

Sub test2()
'ActiveX Control
    wsname = "Hoja8"
    With Sheets(wsname).Cmb_LetterID
        LetterID = .List(.ListIndex)
    End With
End Sub

See my examples:
https://www.dropbox.com/s/pc24s6tz3ud34qo/combos.xlsm?dl=0

 

[mthomas57]

Interesting indeed. I did find a solution that is working for me. Basically I had to Dim dd as an Object. Thanks again so much for your help!

Code:

Dim ws As Worksheet
Dim dd As Object

WSName = ActiveSheet.Name
Set ws = ThisWorkbook.Worksheets(WSName)
Set dd = ws.DropDowns("Cmb_LetterID")
LetterID = dd.List(dd.ListIndex)

 

[DanteAmor]

By the way, in vba it is not necessary to declare variables, if they are declared they must be done correctly since they can produce errors.

Im glad to help you!