Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Change Event
Hi All,
I have 20 Check boxes Named C100 thru C120 for all the checkboxes i have: Private Sub C100_change() Macro1 End Sub thru ..... Private Sub C120_change() Macro1 End Sub instead of repeating this afor all 20 checkboxes is there any other way to do this? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Change Event
Soniya,
there is no concept of control arrays in VBA as there is in VB, but it can be simulated. Create a class module, and call it cControlArray, and add this code Option Explicit Public WithEvents CheckboxGroup As msforms.CheckBox Private Sub CheckboxGroup_Click() MsgBox CheckboxGroup.Caption & _ IIf(CheckboxGroup.Value, " has been set", " has been unset") End Sub The code in the click event should be substitued for your real code, but is used to show you how to access properties of the chosen checkbox. The add a normal code module and add this code Option Explicit Dim aryCBs() As New cControlArray Sub ShowForm() UserForm1.Show End Sub This setsup the pseudo-control array with your form checkboxes, and then shows the form. Change Userform1 to your form name. and in the userform add this code Private Sub UserForm_Initialize() Dim cCBs As Integer Dim ctl As Control cCBs = 0 For Each ctl In Me.Controls If TypeName(ctl) = "CheckBox" Then cCBs = cCBs + 1 ReDim Preserve aryCBs(1 To cCBs) Set aryCBs(cCBs).CheckboxGroup = ctl End If Next ctl End Sub -- HTH Bob Phillips "Soniya" wrote in message ups.com... Hi All, I have 20 Check boxes Named C100 thru C120 for all the checkboxes i have: Private Sub C100_change() Macro1 End Sub thru ..... Private Sub C120_change() Macro1 End Sub instead of repeating this afor all 20 checkboxes is there any other way to do this? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Change Event
Are these checkboxes on a worksheet?
If yes, you could replace them with checkboxes from the forms toolbar and assign each checkbox the same macro. Inside your macro, you can determine which checkbox is being checked: Option Explicit Sub macro1() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) With CBX MsgBox .Name & vbLf & .Value & vbLf & .TopLeftCell.Address End With End Sub Soniya wrote: Hi All, I have 20 Check boxes Named C100 thru C120 for all the checkboxes i have: Private Sub C100_change() Macro1 End Sub thru ..... Private Sub C120_change() Macro1 End Sub instead of repeating this afor all 20 checkboxes is there any other way to do this? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox Event | Excel Discussion (Misc queries) | |||
Click Checkbox Event | Excel Programming | |||
Trigger an event on Checkbox change | Excel Programming | |||
Trigger an event on Checkbox change | Excel Programming | |||
CheckBox Event | Excel Programming |