setting combobox style using vba

[michael.beckinsale]

Hi all,

I have used the following code to set the linked cell for the
comboboxes in my spreadsheet but now want to set the Style to
fmDropDownList but when l insert that line it causes an error. Can
anybody help please?

Sub setlinkedcell()


Dim ole As OLEObject
Dim RowNo As Long
RowNo = 11
For Each ole In ActiveSheet.OLEObjects
If TypeOf ole.Object Is MSForms.ComboBox Then
If ole.Name Like "ComboBox*" Then
ole.LinkedCell = "H" & RowNo
ole.Style = fmStyleDropDownList >>>> causes error
End If
End If
Next

End Sub

TIA

Kind regards

Michael Beckinsale

 

[michael.beckinsale]

Hi All,

Problem solved. Not sure exactly why but changed MSForms.ComboBox to
MSForms.ListBox and worked fine!!!!!!!!

 

[NickHK]

From your code, if you only change MSForms.ComboBox to MSForms.ListBox, then
you receive no error, because that line of code never executes, as no
ListBox has a .Name Like "ComboBox*".
Also ListBoxes do not have a Style property anyway.

I seem to think that you cannot change the .Style property at run-time,
hence the error. But I may wrong, as I can't find any evidence for that at
the moment, apart from the constant error.

NickHK

 

[Dave Peterson]

Maybe...

ole.Object.Style = fmStyleDropDownList

 

[michael.beckinsale]

Dave / Nick,

Many thanks for your input, all appears to be working fine subject to
more extensive testing.

Heres the code l have used:

Sub setlinkedcell()

Dim ole As OLEObject
Dim RowNo As Long
RowNo = 10
For Each ole In ActiveSheet.OLEObjects
If TypeOf ole.Object Is MSForms.ComboBox Then
If ole.Name Like "ComboBox*" Then
ole.Object.LinkedCell = "H" & RowNo
ole.Object.MatchEntry = fmMatchEntryNone
ole.Object.Style = fmStyleDropDownList
ole.Object.MatchRequired = True
RowNo = RowNo + 1
End If
End If
Next

End Sub

Once again many thanks

 

[Dave Peterson]

I think I'd do some more testing.

Not everything needs the .object added (check the .linkedcell line).