Checkbox Macro to Identify the Linked Cell of the Checkbox in Excel 2010

[lostitagain]

I am attempting to use checkboxes on a worksheet that if checked will increase the value of the cell 3 columns to the right of the linked cell by 10.

I have many (>20) checkboxes on this sheet so I want to write one macro that will identify the linked cell of the checkbox clicked and then update the cell located 3 columns to the right of the linked cell (Range("insert linked cell here").Offset(0,3)).

If the cell was checked and becomes unchecked it should decrease the same cell by 10.

The checkboxes are currently Form controls but I can switch them to activeX if necessary.

any help would be appreciated.

 

[Wolfshead]

Hi - hope the below helps
Sub BoxClick()
x = Application.Caller 'The name of the control which started it
Set cb = ActiveSheet.Shapes(x)
stradd = cb.ControlFormat.LinkedCell
If cb.ControlFormat.Value = 1 Then 'Switched on
Range(stradd).Offset(0, 3) = Range(stradd).Offset(0, 3) + 10
Else
Range(stradd).Offset(0, 3) = Range(stradd).Offset(0, 3) + 30
End If

End Sub

 

[lostitagain]

Thanks for the reply. Here's the solution I ended up going with:

Sub CheckBox_Click()
Dim Location As Range
Dim ctrl As Object

Set ctrl = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object
Set Location = Range(ctrl.LinkedCell)

End Sub

That gave me the location of the linked cell and then I am able to use offset and if statements to do anything else I need to the cell which has the value I would like to change.

 

[JLGWhiz]

Thanks for the reply. Here's the solution I ended up going with:

Sub CheckBox_Click()
Dim Location As Range
Dim ctrl As Object

Set ctrl = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object
Set Location = Range(ctrl.LinkedCell)

End Sub

That gave me the location of the linked cell and then I am able to use offset and if statements to do anything else I need to the cell which has the value I would like to change.

Another way to skin the cat. FWIW.

Code:
Sub ckLink()
With Sheets(1)
For Each objx In .OLEObjects
If TypeName(objx.Object) = "CheckBox" Then
If objx.Object.Value = True Then
Range(objx.LinkedCell).Offset(0, 3) = "Hello"
End If
End If
Next
End With
End Sub