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

Hello Soniya,

Each check box is an independent control. Without events, the system would never know which control had changed state. It's the nature of the beast. Sorry, no shortcuts.

Sincerely,
Leith Ross

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" <Melepoil@gmail.com> wrote in message
news:1114517570.102700.16040@z14g2000cwz.googlegroups.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
>

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