setting combobox style using vba
 

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

setting combobox style using vba
 

Hi All,

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

michael.beckinsale wrote:
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

setting combobox style using vba
 

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

"michael.beckinsale"
groups.com...
Hi All,

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

michael.beckinsale wrote:
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

setting combobox style using vba
 

Maybe...

ole.Object.Style = fmStyleDropDownList



"michael.beckinsale" wrote:

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

--

Dave Peterson

setting combobox style using vba
 

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

setting combobox style using vba
 

I think I'd do some more testing.

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

"michael.beckinsale" wrote:

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