setting combobox style using vba

  • Thread starter michael.beckinsale
  • Start date
M

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
 
M

michael.beckinsale

Hi All,

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

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
 
M

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
 
D

Dave Peterson

I think I'd do some more testing.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top