mimicking a control array
 

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

mimicking a control array
 

Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl.Object

mark wrote:

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


--

Dave Peterson

mimicking a control array
 

Thanks!

At first, when I did this, it told me that I had a type mismatch.

But, that led to me realizing that I had set the type on the class module
ComboBoxGroup declaration incorrectly (I had orginally thought it was
ComboBox, but then thoguht it would be OLEObject, but no, it's ComboBox!)

And now it works great.

Thanks!

"Dave Peterson" wrote:

Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl.Object

mark wrote:

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


--

Dave Peterson

mimicking a control array
 

apparently this gets thrown out of scope when you turn on the design mode.

but ok.

"Dave Peterson" wrote:

Set cboBoxes(cboBoxCount).ComboBoxGroup = ctl.Object

mark wrote:

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


--

Dave Peterson