Change event for multiple checkboxes

[Harald Witmer]

My userform contains about 547 check boxes representing
the weeks during a project. The user is supposed to check
or to uncheck the checkboxes conform his/her desire. Based
on each checkbox change a certain number of calculations
will have to be performed. I am looking for a method to
combine the change of one of the checkboxes into one
procedure (i.s.o. 547 subs). Ofcourse within the sub it
should be possible to know which checkbox initiated this
event, in order to perform the right calculations.

Any help would be appreciated.

Harald Witmer

 

[Tom Ogilvy]

What kind of checkbox. The method would be entirely different for checkboxes
from the Control Toolbox Toolbar and those from the Forms Toolbar (the
latter would be much simpler).

Also are the checkboxes on a worksheet - I would assume so.

For control toolbox toolbar, look at the method John Walkenbach documents:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine
this can be adapted for the checkbox click event.

for those from the forms toolbar, you assign all checkboxes to the same
macro. In the macro, you can determine the checkbox firing the macro with
application.Caller

Sub Chkbox_click()
Dim chkbx as Checkbox
set chkbx = Worksheets("Sheet1").Checkboxes(Application.Caller)
msgbox chkbx.Caption " was clicked"
End Sub

as a simple example.

 

[Harald Witmer]

Thank you very much for your prompt and usefull response.
My problem regarded a userform with check boxes.
Tip 44 of the walkenbach documents was very helpfull.
Within excel there are more objects with the name CheckBox so I have to
use msforms.checkbox, and this works great.

Harald Witmer

 

[Ali Emre Hortacsu]

Hi there
I've got the same set of Checkboxes as you do
I read the replies and tip 44 but couldn't make so much
out of them would you be willing to help me
my code is really short :


Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox1.Caption = "Avaliable"
End If
If CheckBox1.Value = False Then
CheckBox1.Caption = "Not Avaliable"

Dim Who, Title, Person, MyDate, When
Who = .......

I've to add this to all of my Checkboxes

Thanks!