Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem setting combobox | Excel Programming | |||
Setting properties to combobox | Excel Programming | |||
Combobox setting | Excel Programming | |||
Setting ComboBox Value | Excel Programming | |||
Changing the Style of a Combobox Via It's Name | Excel Programming |