Checkboxes on a Shared Worksheet!

[gr8guy]

Private Sub Reset_Click()
Dim oObj as OleObject
for each oObj in Me.OleObjects
if typeof oObj.Object is MSForms.Checkbox then
oObj.Object.Caption = ""
end if
Next
End Sub

--
Regards,
Tom Ogilvy
--------------------------------------------&&&-----------------------------
---------------------------------------
Thanks again to Tom Ogilvy for the above code which works beautifully, MADE
MY WORK A LITTLE SIMPLER & learn something new from this wonderful VBA coder
writer.

Hi Tom,

Suppose a lot of (around 250) checkboxes on a shared workbook's worksheet,
named "Break Tracker". for each checkbox, i have the following code entered
to automate the checkbox's Click event. So that when any user logged in to
the Workbook & clicking on any checkbox will automatically enter the system
time on another worksheet "Break Record" which will have the same format &
the same cell address as the "Break Tracker", so that any user clicking
checkbox embedded on cell say, A5 on "Break Tracker", will enter the system
time on cell A5 on "Break Record" also.

but the main problem lies in the number of checkboxes (around 250) which is
making the sheet very slow to save & since its also a shared workbook with
many users having it open & updating (clicking) their respective checkboxes.

I have made the usual layman way code for every checkbox:

Private Sub Checkbox1_Click() ' which is placed on the D5 cell of
"Break Tracker".
Application.Screenupdating = False
Checkbox1.Caption = Now
Checkbox1.Enabled = true ' which can be later made to FALSE so that user
cannot change the time logged in, again.

' There is a linkage to similar cells in another worksheet "Calculation"
which will 'show whether user clicked the checkbox '(True/False) or not.
Checkbox1.Linked Cell = "=Calculation!D5"

Worksheets("Break Record").Range("D5").Value = Checkbox1.Caption
Application.Screenupdating = True
End Sub

Now there is a similar code for every other checkbox on the Worksheet "Break
Tracker", so that it is BULKY, TIME & MEMORY CONSUMING.
like this:

Private Sub Checkbox2_Click() ' which is placed on the E5 cell of
"Break Tracker".
Application.Screenupdating = False
Checkbox2.Caption = Now
Checkbox2.Enabled = true
Checkbox2.Linked Cell = "=Calculation!E5"

Worksheets("Break Record").Range("E5").Value = Checkbox2.Caption
Application.Screenupdating = True
End Sub

and so on & on......................

Is it possible to have one code which can be called on Click Event for every
embedded Checkbox on the sheet & do the same i.e. enter the system time in
the corresponding Similar cell address on the other sheet ("Break Record") &
also show status (true/false) of checkbox in again a Similar cell address on
yet another sheet ("Calculation").

I read the help file for OleObject Collection, but donot know how to go
about doing it. But it has to do something with accessing the Index or the
Item number of the particular Checkbox which was clicked & then doing the
following as shown above.


Thanks & Best Regards,

Eijaz Sheikh