I have created in my sheet a check box with a linked cell to it.

When I click on this control a macro is activated.

I would like to know how is possible to know the value of the linked
cell.

The purpose of this is to know the row where the check box is
positioned, since the linked cell is on the same row of the control.

Thanks.
Maurizio


--
gm139
------------------------------------------------------------------------
gm139's Profile: http://www.excelforum.com/member.php...o&userid=26951
View this thread: http://www.excelforum.com/showthread...hreadid=508691

Hi Maurizio,

Sub test()
Dim LinkCellAddr As string
Dim CheckboxRow As Long, LinkedCellRow As Long
Dim cbx As CheckBox

Set cbx = ActiveSheet.CheckBoxes(Application.Caller)
'On Error GoTo errH

CheckboxRow = cbx.TopLeftCell.Row

s = "CheckboxRow " & CheckboxRow & vbCr & "LinkedCellRow "

LinkCellAddr = cbx.LinkedCell

If Len(LinkCellAddr) Then
LinkedCellRow = Range(LinkCellAddr).Row
s = s & LinkedCellRow
Else
s = s & " no linked cell"
End If

MsgBox s

errH:
End Sub

Above assumes macro assigned to a Forms Checkbox. If your Checkbox is an
ActiveX control form the Controls toolbox you can use almost same code

Private Sub CheckBox1_Click()
Dim cbx As Object

Set cbx = CheckBox1

' code as in test above

End Sub

Regards,
Peter T

"gm139" wrote in
message ...

I have created in my sheet a check box with a linked cell to it.

When I click on this control a macro is activated.

I would like to know how is possible to know the value of the linked
cell.

The purpose of this is to know the row where the check box is
positioned, since the linked cell is on the same row of the control.

Thanks.
Maurizio


--
gm139
------------------------------------------------------------------------
gm139's Profile:
http://www.excelforum.com/member.php...o&userid=26951
View this thread: http://www.excelforum.com/showthread...hreadid=508691

Sub CheckBox1_Click()
Debug.Print ActiveSheet.Shapes("Check box 1").ControlFormat.LinkedCell
End Sub



Sub GetCellLinks()
Dim sh As Shape

For Each sh In ActiveSheet.Shapes
Debug.Print sh.Name & " = " & sh.ControlFormat.LinkedCell
Next
End Sub


"gm139" wrote in
message ...

I have created in my sheet a check box with a linked cell to it.

When I click on this control a macro is activated.

I would like to know how is possible to know the value of the linked
cell.

The purpose of this is to know the row where the check box is
positioned, since the linked cell is on the same row of the control.

Thanks.
Maurizio


--
gm139
------------------------------------------------------------------------
gm139's Profile:
http://www.excelforum.com/member.php...o&userid=26951
View this thread: http://www.excelforum.com/showthread...hreadid=508691

Thank you!!

--
gm13
-----------------------------------------------------------------------
gm139's Profile: http://www.excelforum.com/member.php...fo&userid=2695
View this thread: http://www.excelforum.com/showthread.php?threadid=50869