Help with Linkcheckbox macro

Hello,

I am using the following macro code I got of the internet to add hundreds of checkboxes and link them. It works fine, except I only want a linked checkbox on every 10th row (ie, check box on row 9, then row 19, etc) -- how do I change the below code to do this? THANKS


Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Offset(0, lCol).Address
End With
Next chk

End Sub

The posted code doesn't add any checkboxes, it assigns the linked cell to existing checkboxes.
I'm guessing you're wanting checkboxes in column A, 10 rows apart, starting at row 9 and their linked cell to be on the same row as the checkbox in column C.
If not, you'll need to expand on what you're wanting.

NoS,

Thank you so much for getting back to me. I would like a new code that inserts checkboxes every 10th row starting in cell F9. So next checkbox in F19, etc. And then links them to H9 (with True/False). See attached image. Does this make sense? Apologies, I am a Excel/code rookie.

Thanks,
Chris

_____________________________________________

NoS said:

The posted code doesn't add any checkboxes, it assigns the linked cell to existing checkboxes.
I'm guessing you're wanting checkboxes in column A, 10 rows apart, starting at row 9 and their linked cell to be on the same row as the checkbox in column C.
If not, you'll need to expand on what you're wanting.

Code:

Sub PutIn_CheckBoxes()

    Dim CBX As CheckBox
    Dim i As Long
    Dim myCell As Range
    
' this line deletes ALL existing checkboxes from sheet, links are NOT cleared.
ActiveSheet.CheckBoxes.Delete

For i = 9 To 100 Step 10
    Set myCell = Cells(i, 1)
    With myCell
        Set CBX = .Parent.CheckBoxes.Add _
                    (Top:=.Top, Left:=.Left, _
                     Width:=.Width, Height:=.Height) 'click area same size as cell
        CBX.Name = "CBX_" & .Address(0, 0)
        'CBX.Caption = "Label goes here"         'whatever you want, "" for none
        CBX.Value = xlOff                       'initial value unchecked
        CBX.LinkedCell = .Offset(0, 2).Address  '<~~~~~ offset to linked cell
    End With
Next i
End Sub

Click to expand...

In the line change the 1, which is for first Column, to 6 for the sixth column, which will be F.

NoS, thank you again for this help, I really appreciate it. Your correction worked perfectly. I am now trying to set up a separate work sheet so that when I click a check box (in the first worksheet), a cell value is displayed on the new worksheet (5th worksheet).

I've been using this formula on the new worksheet: =IF('TAB 1 - SUPV ANALYSIS TOOL'!H9=TRUE,'TAB 1 - SUPV ANALYSIS TOOL'!C9,"").

The problem is that when I drag this formula to copy it for many rows, and then click several checkboxes (the checkboxes are only every 10th row, on purpose and per your macro code), the values come up 10 rows apart on the new worksheet. I'd like the values to come up on the new worksheet, but in a list with no empty row gaps.

Any help greatly appreciated!!

Assuming your first formula goes into row 1 on sheet5,
try a formula along the lines of this then drag down

The Row() equates to the row the formula is in, so in the first row (ROW()*10)-1) is 9 and in the second row it's 19.

Hope that helps.

Thank you for the help!

The formula is giving me a #REF error when I put it in sheet 5, Row1 column A -- I am wondering if I need to replace sheet1!H with the link to my actual sheet 1 somehow??

Thanks for any help you can provide


--------------------------------

NoS said:

Assuming your first formula goes into row 1 on sheet5,
try a formula along the lines of this then drag down

Code:

=IF(INDIRECT("Sheet1!H" & (ROW()*10)-1)=TRUE,INDIRECT("Sheet1!C" & (ROW()*10)-1),"")

The Row() equates to the row the formula is in, so in the first row (ROW()*10)-1) is 9 and in the second row it's 19.

Hope that helps.

Click to expand...