You can use a single macro if you use checkboxes from the Forms toolbar.
You could use 11 macros that call a single macro that does the real work if you
use checkboxes from the Control toolbox toolbar.
For checkboxes from the Forms toolbar, you could use a macro like this (placed
in a general module):
Option Explicit
Sub testme()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
CBX.TopLeftCell.Offset(0, 1).Value = 16
Else
CBX.TopLeftCell.Offset(0, 1).Value = 8
End If
End Sub
For checkboxes from the Control toolbox toolbar, you could use something like:
Option Explicit
Private Sub CheckBox1_Click()
Call DoTheWork(Me.CheckBox1)
End Sub
Private Sub CheckBox2_Click()
Call DoTheWork(Me.CheckBox2)
End Sub
Private Sub DoTheWork(CBX As MSForms.CheckBox)
If CBX.Value = True Then
CBX.TopLeftCell.Offset(0, 1).Value = 16
Else
CBX.TopLeftCell.Offset(0, 1).Value = 8
End If
End Sub
You'd need 11 of those _click events that call the single DoTheWork subroutine.
And all these procedures would be in the worksheet module (although, the
DoTheWork procedure could live in a General module -- but remove the Private
from the definition).
That worked great! Thanks.
I would like to re-ask the question, though. In an effort to help learn VBA
in Excel and different ways to do things, can a single macro be written that
can look at each checkbox, evaluate it (true/false), and place its respectful
value in the appropriate cell? I am thinking it can be down, but somehow the
macro must look at the name of the checkbox and pull out the numeric value,
checkbox1, 2, 3, etc. before making further determinations. I just do not
know how to get the name.
Thanks again for you help?
Les