Excel 2007 Insert Check Boxes to an Entire Column

[Marilyn]

Hello,

I'm creating a form in Excel 2007 and I have to insert a column of check
boxes. Does anyone know if there's a way to insert check boxes to an entire
column without inserting each one manually?

Thanks,

 

[Jacob Skaria]

Dear Marilyn

You can play around with fonts to acheive this..

1. Adjust the colum width to look like a square box.
2. Set the font for that column to Marlett
3. Type lower case 'a' for tick mark or 'r' for cross mark.
4. Apply borders if you would like to

 

[Marilyn]

Hi Jacob,

Thank you so much for the wonderful feedback and it does work exactly as you
say it, however, I need to have people fill out the form and the check boxes
would be more convenient.

Do you know if this can be accomplish with a macro?

Thanks again,

 

[Dave Peterson]

This adds checkboxes from the Forms toolbar to column A rows 2 to 30.

Option Explicit
Sub Testme()

Dim CBX As CheckBox
Dim myRange As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim WhatCol As String

Set wks = ActiveSheet

FirstRow = 2
LastRow = 20
WhatCol = "A"

With wks

'remove any existing checkboxes
.CheckBoxes.Delete

For iRow = FirstRow To LastRow
With .Cells(iRow, WhatCol)
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Height:=.Height, _
Width:=.Width)
CBX.LinkedCell = .Address(external:=True)
.NumberFormat = ";;;"
End With
With CBX
.Name = "CBX_" & .TopLeftCell.Address(0, 0)
.Caption = ""
End With
Next iRow
End With
End Sub

It also assigns the linke cell to the cell that holds that checkbox -- but by
using a number format of ";;;", it's hidden from the worksheet. You can see it
in the formulabar when you select the cell.