LinkedCell to Checkbox calling onAction Subroutine in VBA?

Question

I have a great number of checkboxes calling a subroutine whenever a checkbox is ticked/unticked. Now I would like to get the address to the linked cell of the checkbox that called the sub. I've tried

Application.Caller.LinkedCell

but I get "Object required" error, I guess because Application.Caller returns a string.

Is there some way I can get the address of the linkedCell?

Thank you for any help!

Answer 1

This assumes you are using a Checkbox from the Forms menu, as I don't think Application.Caller recognizes an ActiveX control. It uses the oddly-named ControlFormat property of a Shape (A Checkbox is a Shape):

Sub GetLinkedCell()
Dim shp As Shape
Dim chk As ControlFormat

Set shp = ThisWorkbook.Worksheets(1).Shapes(Application.Caller)
Set chk = shp.ControlFormat
MsgBox chk.LinkedCell
End Sub