How do you replicate checkboxes with Format Control applied?

[timmy2]

In cell E2 I've inserted a checkbox. Then I applied a Format Control to it so that TRUE/FALSE will appear next to it in F2.

Now I want to replicate that checkbox down the column with each checkbox displaying TRUE or FALSE to the right of it.

I can replicate the checkboxes but checkmarking one box checks them all and switches TRUE/FALSE only in F2.

 

[Sam_ExcelVault]

Hi Timmy

Are you copy and pasting from the original checkbox and then subsequent check boxes? If so, they will inherit the format control as well, meaning they would all possess the same cell link. You'll need to update the cell link for each one to point towards the desired cell.

 

[NoSparks]

perhaps this code (from the forums a long, long time ago) will be of use

Private Sub Insert_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, links are NOT cleared.
    .CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.
    Set myRng = .Range("E2:E20")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        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, 1).Address  '<~~~~~ offset to linked cell
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub

 

[timmy2]

Hi Timmy

Are you copy and pasting from the original checkbox and then subsequent check boxes? If so, they will inherit the format control as well, meaning they would all possess the same cell link. You'll need to update the cell link for each one to point towards the desired cell.

Thank you for replying, Sam_ExcelVault. I did indeed try copying/pasting but as you acknowledge, you must then manually update the cell link for each one, a time-consuming process that inspired my original post.

 

[timmy2]

THANK YOU, NoSparks! That does the trick. Your help is greatly appreciated.

When it's open in the VBA editor I can run it. Would you happen to know why the resulting macro doesn't show up in the list of macros available (ALT+F8)? I inserted it as a module and saved the sheet as Excel Macro-Enabled Workbook.

 

[NoSparks]

If you want it to show in the macro dialogue, remove the word Private.

I don't really think you want to do that, as-is it removes all checkboxes from the sheet and is hard coded as to where checkboxes are desired.

 

[timmy2]

Your reasoning is excellent. I was unaware of the drawbacks. Still learning VBA and macros. Thank you again.