Obtaining Checkbox Name

[Guest]

Hi - I have a form with a large number of identical checkboxes on it. All
were created from the Forms toolbox. I would like to have one macro service
all checkboxes when they are checked or unchecked, however I have been unable
to figure out how to obtain the name of the invoking checkbox.

Thanks,

 

[Guest]

MsgBox ActiveSheet.CheckBoxes(Application.Caller).Caption

Regards,
Greg

 

[Guest]

Hmmmm - Thanks... But that gets the caption (or label) but still does not
return the name of the checkbox so I can use it to test for Checked or
UnChecked.

 

[Guest]

The code was intended only as a demo to show that the identity could be
captured. The Application.Caller statement returns the name of the object
that called the running procedure as a text string - i.e. Application.Caller
by itself is what you are looking for. (Run MsgBox Application.Caller).

Regards,
Greg

 

[Peter T]

Sub Checkbox1_Click()
Dim sValue
With ActiveSheet.CheckBoxes(Application.Caller)
Select Case .Value
Case xlOn: sValue = "checked"
Case xlOff: sValue = "un-checked"
Case xlMixed: sValue = "mixed"
End Select

MsgBox "Name " & .Name & vbCr & _
"Caption " & .Caption & vbCr & _
"Checked " & .Value & " " & sValue
End With
End Sub

Regards,
Peter T

 

[Guest]

Thanks for the responses guys. You pointed me in the right direction and now
I have it mostly figured out. Much appreciated.

I do note that it seems that VBA works one way when the checkbox was created
with the forms toolbox and another when the checkbox was created with the
Control Toolbox... very confusing! Is there a good book out there with
examples you can recommend?