Retrieve the ControlFormat.LinkedCell value for use in VBA formula

[mattlightbourn]

Hi,
I have a set of Form Control check boxes on my Excel sheet which I have assigned to macros. However, for each row, I had had to create individual macros per row. Instead, I would like to just have the two macros below and use the format control cell link row number to set the checkRow value as below.

I have looked all over the various forums and either my search is too ambiguous or not specific enough. What I want to do is set the value of checkRow below by retrieving the LinkedCell value from the checkbox that I selected when clicking on it. Please can you help? Thanks, Matt

Sub CheckBox4_Click()
Dim checkRow As Long
checkRow = ??????????????
If Cells(checkRow, 8).Value = False Then
Cells(checkRow, 6).Value = True
Cells(checkRow, 7).Value = True
Cells(checkRow, 8).Value = True
End If
If Cells(checkRow, 8).Value = True Then
Cells(checkRow, 8).Value = True
End If
End Sub

Sub CheckBox3_Click()
Dim checkRow As Long
checkRow = ??????????????
If Cells(checkRow, 7).Value = False Then
Cells(checkRow, 6).Value = True
Cells(checkRow, 7).Value = True
End If
If Cells(checkRow, 7).Value = True Then
Cells(checkRow, 7).Value = True
End If
End Sub

 

[mikerickson]

You can assign this macro to all of your checkboxes. I think it will give you what you need to modify your code.
When working with Form controls, the .ControlFormat of the Shape object is often used.

Sub test()
    With ActiveSheet.Shapes(Application.Caller)
        MsgBox .Name & " is linked to " & .ControlFormat.LinkedCell
    End With
End Sub

 

[mattlightbourn]

awesome thanks - it works (I inserted it into my script to check) however, my next mission is to get the ControlFormat.LinkedCell value into a range variable so that I can set another variable (checkRow) to be the Row number. Any ideas? I'm sure there's a one liner in there rather than me using two variables but my brain is bleeding

Sub CheckBox4_Click()
Dim checkRow As Long
checkRow = 6
    With ActiveSheet.Shapes(Application.Caller)
        MsgBox .Name & " is linked to " & .ControlFormat.LinkedCell
    End With
If Cells(checkRow, 8).Value = False Then
Cells(checkRow, 6).Value = True
Cells(checkRow, 7).Value = True
Cells(checkRow, 8).Value = True
End If
If Cells(checkRow, 8).Value = True Then
Cells(checkRow, 8).Value = True
End If
End Sub

 

[mattlightbourn]

hehe, my attempt was the following, but doesn't work, unfortunately I have to bastardise scripts and cobble them together since I am not experienced enough to create from scratch, trying to learn on the job, not always the best way thanks for your help

Sub CheckBox4_Click()
Dim checkRow As Long
Dim shapeRef As Range
    With ActiveSheet.Shapes(Application.Caller)
    shapeRef.Value = .ControlFormat.LinkedCell
    End With
    checkRow.Value = shapeRef.Row
If Cells(checkRow, 8).Value = False Then
Cells(checkRow, 6).Value = True
Cells(checkRow, 7).Value = True
Cells(checkRow, 8).Value = True
End If
If Cells(checkRow, 8).Value = True Then
Cells(checkRow, 8).Value = True
End If
End Sub

 

[mikerickson]

Try this. There isn't really a need to be using intermediate values (like checkRow)
All of that information is contained in the Shape. If you're only going to use the info once, there's no need to preserve it.

With ActiveSheet
    With .Range(.Shapes(Application.Caller).ControlFormat.LinkedCell).EntireRow
        If .Cells(1, 8).Value = False Then
            .Cells(1, 6).Resize(1, 3)Value = True
        End If
    End With
End With

 

[mattlightbourn]

thanks for this, I have three checkboxes per row and I want to setup only 3 scripts as opposed to 3 x number of rows. So, depending on the checkbox, will provide the variable I need to affect data in a specific row which is the same row that the linkedcell is attributed to the form control.

So if my script wants to apply a data change to Cells(checkRow, 8) when the checkbox on row 4 is selected, the data change is applied to Cells(4,8) because checkRow value is set to 4.

I need to make checkRow variable =
.Range(.Shapes(Application.Caller).ControlFormat.LinkedCell).EntireRow

Thanks, Matt
</pre>