Control Arrays?

[SixSigmaGuy]

Can I have control arrays in Excel VBA? I have added a bunch of checkboxes
to my worksheet and I want to put the exact same code behind each checkbox's
click event. Is there any way I can manage them without having to duplicate
code?

 

[Tom Ogilvy]

Control Arrays are not supported in VBA, but you might have a look at this
technque documented by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

This is for commandbuttons, but works as well for other MSforms 2.0 controls
such as checkboxes.

 

[Jan Karel Pieterse]

Hi SixSigmaGuy,

Can I have control arrays in Excel VBA? I have added a bunch of checkboxes
to my worksheet and I want to put the exact same code behind each checkbox's
click event. Is there any way I can manage them without having to duplicate
code?

No, unless you use a class module somehow.

If you would have used checkboxes from the forms toolbar, you could assign
them all to the same macro and use Application.Caller in the sub to determine
which was clicked.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Guest]

Hi,

I believe you'll have to write at least each click event sub, but you can
have it redirected to a common sub:
'---------------------------------
Private Sub CheckBox1_Click()
ProcessCheckBox CheckBox1
End Sub

'... one above _Click for each checkbox

Sub ProcessCheckBox(Chk As MSForms.CheckBox)
'code for Chk here
End Sub
'-------------------------------------

Another way would be to use a checkbox from the Forms toolbar instead of the
Control Toolbox toolbar. Assign them a common macro say Sub ProcessCheckBox2:
'------------------------------------------
Sub ProcessCheckBox2
Dim chk As CheckBox
'use Caller to determine which one triggered this macro
Set chk = ActiveSheet.CheckBoxes(Application.Caller)
MsgBox chk.Name
End Sub End Sub
'-----------------------------------------------------

Regards,
Sebastien

 

[Jamie Collins]

I believe you'll have to write at least each click event sub, but you can
have it redirected to a common sub

No. As others have suggested, you can use a class module.

As a quick demo, put three ActiveX CheckBoxes on Sheet1 and put this
declaration in a new/blank class module called Class1:

Private WithEvents m_Chk As MSForms.CheckBox

In the code module, drop the (General) dropdown and select the m_Chk
variable. Now in the right hand dropdown (previously Declarations) you
have the control's event handlers. For example, add this code:

Private Sub m_Chk_Click()
MsgBox m_Chk.Name
End Sub

Now imagine you had three instances of this class and the m_Chk.Name
variable was pointing at a different CheckBox for each instance. You'd
need a method to be able to make this association:

Public Function Init(ByVal CheckBox As MSForms.CheckBox) As Boolean
Set m_Chk = CheckBox
End Function

You could use the class in the ThisWorkbook code module like this:

Private Chk1 As Class1
Private Chk2 As Class1
Private Chk3 As Class1

Private Sub Workbook_Open()
Set Chk1 = New Class1
Set Chk2 = New Class1
Set Chk3 = New Class1
Chk1.Init Sheet1.CheckBox1
Chk2.Init Sheet1.CheckBox2
Chk3.Init Sheet1.CheckBox3
End Sub

Run the Workbook_Open sub and now clicking one of the CheckBoxes is
now handled by the same piece of code.

Jamie.

--

 

[Jamie Collins]

(e-mail address removed) (Jamie Collins) wrote ...

You could use the class in the ThisWorkbook code module like this:

Private Chk1 As Class1
Private Chk2 As Class1
Private Chk3 As Class1

I forgot to address the subject of the thread! The above declarations
could alternatively be put into an array e.g.

Private Chks(2) As Class1

Private Sub Workbook_Open()

Set Chks(0) = New Class1
Set Chks(1) = New Class1
Set Chks(2) = New Class1
Chks(0).Init Sheet1.CheckBox1
Chks(1).Init Sheet1.CheckBox2
Chks(2).Init Sheet1.CheckBox3

End Sub

Jamie.

--