Trying to Automatically Insert Checkboxes

[rpriestley]

I would like to Automatically Insert checkboxes in column V3:V169 and link to Column AC3:AC169. I found this code on web can anyone help me as it does not seem to work when I run Macro



Option Explicit
Sub insertCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String

cellRange = InputBox(Prompt:="V3:V169", _
Title:="")

linkedColumn = InputBox(Prompt:="AC", _
Title:="Linked Column")

cboxLabel = InputBox(Prompt:="Checkbox Label", _
Title:="Checkbox Label")

With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
Width:=.Width, Left:=.Left, Height:=.Height)

With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
End With

.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

 

[Tom Urtis]

Think for a moment about why you need so many checkboxes. Just for this task alone you are embedding 167 checkboxes when there is probably a better and less cumbersome way to accomplish whatever you are doing. Try describing your worksheet in more detail, as it is rarely necessary for that many embedded controls to be required.

 

[rpriestley]

Individual Sheet for Wedding Reply List, Anyway got this macro working but I am having problems with this delete macro

Sub deleteCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As String

cellRange = InputBox(Prompt:=””, _
Title:=”CellRange”)

With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
ActiveSheet.Shapes(“checkbox_” & myCell.Address(0, 0)).Delete
End With
Next myCell
End With
End Sub