Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control array | Excel Programming | |||
Control Array | Excel Programming | |||
Mimicking an GroupBy Clause in Excel | Excel Programming | |||
Control Array Help... | Excel Programming | |||
Control array - does it exist in VBA as in VB? | Excel Programming |