How to copy checkboxes down a column?

I figured out how to insert a checkbox into cell I4. I'd like to drag/copy the cell all the way down the column, but it doesn't work this way. How can I do that without individually copy/pasting into each cell?

Better yet, is there a way to make a check box appear only if there is content in the row? This is a spread sheet where I add one or two rows per day. For example, if there is content in B25, make a check box appear in I25? That would help keep the spreadsheet clean.

Thanks,

Dan

Hello earthtodan,

Yes it can be done. However, I need to know where the CheckBox came from, Forms tool bar or Control Toolbox. Also are there any macros associated with these check boxes? Any special formatting of font size, color, etc? Do they display any text?

Sincerely,
Leith Ross

I did it using the forms toolbar, although I don't really know the difference. I'll do it whichever way is best. I don't know how to get the checkboxes to "stick" using the control toolbox.
There is no special formatting associated with them or any code. They are in a column titled "Oversold", and they get checked if a project (row) is oversold. They are going to be counted in a COUNT IF TRUE formula.

Dan

Hello Dan,

Here are the macros to add the check boxes into column "I" if the cell in column "B" isn't empty.

Sub AddCheckBoxToCell(Ref_Cell As Range)
    
  Dim ChkBox As CheckBox
  Dim N As Double
  
    With Ref_Cell.Cells(1, 1)
      refLeft = .Left
      refTop = .Top
      refHeight = .Height
    End With
      
      Set ChkBox = ActiveSheet.CheckBoxes.Add(10, 10, 15, 12)
      
      N = (refHeight - ChkBox.Height) / 2#
      
      With ChkBox
        .Caption = ""
        .Top = refTop + N
        .Left = refLeft
        .OnAction = ""
      End With
    
End Sub

Sub AddCheckBoxes()

  Dim BoxCol As Variant
  Dim CtrlCol As Variant
  Dim LastRow As Long
  Dim R As Long
  Dim StartRow As Long
  
    BoxCol = "I"     'Column where Check Box is inserted
    CtrlCol = "B"    'Column that controls if Check Box is inserted
    StartRow = 1
    LastRow = Cells(Rows.Count, CtrlCol).End(xlUp).Row
    
        For R = StartRow To LastRow
          If Cells(R, CtrlCol) <> "" Then
            AddCheckBoxToCell Cells(R, BoxCol)
          End If
        Next R
            
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Wow, thanks for all that code. I'll test it out as soon as I'm not supposed to be working.

Checkboxes from the Forms toolbox or Control Toolbar are on a different layer of the sheet than cells, so it's easy to mangle their associations. 'Twere I you, I'd use Marlett checkboxes. They are cells with a double-click behavior implemented in about five lines of VBA. A search will turn up many examples.

I know this is an old thread but how do you center the checkbox in the cell? I am very new to VBA.

Originally Posted by Leith Ross

Hello Dan,

Here are the macros to add the check boxes into column "I" if the cell in column "B" isn't empty.

Sub AddCheckBoxToCell(Ref_Cell As Range)
    
  Dim ChkBox As CheckBox
  Dim N As Double
  
    With Ref_Cell.Cells(1, 1)
      refLeft = .Left
      refTop = .Top
      refHeight = .Height
    End With
      
      Set ChkBox = ActiveSheet.CheckBoxes.Add(10, 10, 15, 12)
      
      N = (refHeight - ChkBox.Height) / 2#
      
      With ChkBox
        .Caption = ""
        .Top = refTop + N
        .Left = refLeft
        .OnAction = ""
      End With
    
End Sub

Sub AddCheckBoxes()

  Dim BoxCol As Variant
  Dim CtrlCol As Variant
  Dim LastRow As Long
  Dim R As Long
  Dim StartRow As Long
  
    BoxCol = "I"     'Column where Check Box is inserted
    CtrlCol = "B"    'Column that controls if Check Box is inserted
    StartRow = 1
    LastRow = Cells(Rows.Count, CtrlCol).End(xlUp).Row
    
        For R = StartRow To LastRow
          If Cells(R, CtrlCol) <> "" Then
            AddCheckBoxToCell Cells(R, BoxCol)
          End If
        Next R
            
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

This is very helpful but I have a few questions:

Note: All questions are meant to be included in the macro.

How do I make the size wider so the "Text" next to the box will all shows up?

Can the "Cell Link" be included in the Macro to another column?