system variable representing object or subroutine name?

[Mike Cooper]

Hi,

I am trying to write code that accesses the .LinkedCell property of a
checkbox, from the checkbox_Click subroutine of the checkbox.
That is easy enough:

Sub IssueABox01_Click()
SomeVariable = IssueABox01.LinkedCell

(Where IssueABox01 is the name of my checkbox.)

Here is my challenge. I have 105 checkboxes and I would to create
uniform code so I don't have to customize that code 105 times! What I
need is some kind of default system variable that I can replace
"IssueABox01" with in the second line above; a label or variable that
represents the title of a subroutine (I can edit out the "_Click"
after that. Or else the equivalent of the "Me" form variable for a
currently selected object. I am new to VBA, but intuitively it just
seems like such a variable has to exist. I mean we're "in" the
subroutine right? So excel must "know" it's name!

Can anyone out there tell me if such a variable, and if so, what it
is. My books don't reference one, but then they are very basic.

Thank you,
Mike Cooper

 

[jaf]

Hi Mike,
You can try this. It may help, but be aware you may need a worksheet change
event..

Sub test()
Dim Ctrl As MSForms.Control, i As Long, MyCell(105)
For Each Ctrl In UserForm1.Controls '************change to worksheet
name***************

If TypeOf Ctrl Is MSForms.CheckBox Then
i = i + 1
'Debug.Print "Checkbox", Ctrl.Name
MyCell(i) = Ctrl.LinkedCell
End If
Next Ctrl

End Sub