M
mark
the other day, someone asked me to look at a workbook (that someone else
created) that had a whole host of comboboes in the spreadsheet... not in a vb
userform, but in the sheet... they would have been the type put in from the
control toolbox toolbar, ProgId = "Forms.ComboBox.1"
This workbook had ~50 of these things, and they all had their own little
block of nearly identical code associated with them, repeating the same
thing, over and over, and over.
I did some research and found this nice example of mimicking a control array:
http://www.j-walk.com/ss/excel/tips/tip44.htm
I took that, and adapted it, and have it working right up to the point of
where I assign the event code to the member of the control array:
Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl
and then it tells me:
"Object or class does not support the set of events"
Well that's rather unfortunate.
So, with a class, a control array could be mimicked with controls on a vb
form, but not the forms.combobox.1 type of combobox?
or, am I missing something else I could do?
(I don't really need to do this, but I was repulsed by all that repeated
basically unnecessary, hard to look at, code, so I was doing a little
research)
here's the main block of code, as adapted, which was hoped to work on
OLEObject forms.combobox.1 objects, in the worksheet.
Option Explicit
Dim cboBoxes() As New clsWsComboBox
Sub SetCbos()
Dim ws As Worksheet
Dim cboBoxCount As Integer
Dim ctl As OLEObject
' Create the Button objects
cboBoxCount = 0
Set ws = ActiveSheet
For Each ctl In ws.OLEObjects
If ctl.progID = "Forms.ComboBox.1" Then
ctl.ListFillRange = "tblStates"
If ctl.Name <> "OKButton" Then 'Skip the OKButton
cboBoxCount = cboBoxCount + 1
ReDim Preserve cboBoxes(1 To cboBoxCount)
Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl
End If
End If
created) that had a whole host of comboboes in the spreadsheet... not in a vb
userform, but in the sheet... they would have been the type put in from the
control toolbox toolbar, ProgId = "Forms.ComboBox.1"
This workbook had ~50 of these things, and they all had their own little
block of nearly identical code associated with them, repeating the same
thing, over and over, and over.
I did some research and found this nice example of mimicking a control array:
http://www.j-walk.com/ss/excel/tips/tip44.htm
I took that, and adapted it, and have it working right up to the point of
where I assign the event code to the member of the control array:
Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl
and then it tells me:
"Object or class does not support the set of events"
Well that's rather unfortunate.
So, with a class, a control array could be mimicked with controls on a vb
form, but not the forms.combobox.1 type of combobox?
or, am I missing something else I could do?
(I don't really need to do this, but I was repulsed by all that repeated
basically unnecessary, hard to look at, code, so I was doing a little
research)
here's the main block of code, as adapted, which was hoped to work on
OLEObject forms.combobox.1 objects, in the worksheet.
Option Explicit
Dim cboBoxes() As New clsWsComboBox
Sub SetCbos()
Dim ws As Worksheet
Dim cboBoxCount As Integer
Dim ctl As OLEObject
' Create the Button objects
cboBoxCount = 0
Set ws = ActiveSheet
For Each ctl In ws.OLEObjects
If ctl.progID = "Forms.ComboBox.1" Then
ctl.ListFillRange = "tblStates"
If ctl.Name <> "OKButton" Then 'Skip the OKButton
cboBoxCount = cboBoxCount + 1
ReDim Preserve cboBoxes(1 To cboBoxCount)
Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl
End If
End If