Adding Checkboxes Programmatically

  • Thread starter Thread starter Mark D'Agosta
  • Start date Start date
M

Mark D'Agosta

Anyone know how to programmatically add check boxes to a worksheet? I'd
like to add a check box in the first column of each new row that is added to
a worksheet. The number of rows is not known at design time and the check
box should be completely contained within the cell in the first column. I
had some success using the Worksheet_Change event to create a checkbox, but
am unable to control the positioning of it. Any help will be greatly
appreciated.

Thanks,

Mark D.
 
What kind of checkbox - control toolbox toolbar or forms toolbar

Put you your code that creates the checkbox.
 
Tom,

Don't really care what kind of checkbox; as long as it be linked to an
underlying cell that I can check for True/False values. I've actually made
some progress on this, but the code is kind of long-winded. It would be
nice if I could (A) streamline the code a little bit and (B) find a more
proper way of hiding the underlying TRUE/FALSE rather than changing the text
font color to white.

To test my code, simply create a new workbook, placing the following code in
the ThisWorkbook!Workbook_Open event:
<<
Private Sub Workbook_Open()

Worksheets(1).Cells(2, 2).Value = "Second Row"
Worksheets(1).Cells(3, 2).Value = "Third Row"
Worksheets(1).Cells(4, 2).Value = "Fourth Row"
Worksheets(1).Cells(5, 2).Value = "Fifth Row"
Worksheets(1).Cells(6, 2).Value = "Sixth Row"

End Sub
Then create the following code for the Sheet1!Worksheet_Change event:

<<
Private Sub Worksheet_Change(ByVal Target As Range)
'Place a checkbox in the first column of each row that has data
Static lngLeft As Long
Static lngWidth As Long
Static blnInit As Boolean
Dim lngTop As Long
Dim lngHeight As Long
Dim lngRow As Long

If Not blnInit Then
'Only need to set the Left and Width values of the "A" column once,
since that won't change.
'However, the Top value will change from row to row and the Height value
may change as well.
lngLeft = Range("A1").Left
lngWidth = Range("A1").Width
'Determine the measures that will center the checkbox within the cell.
lngLeft = (lngLeft + lngWidth) / 2
lngWidth = lngWidth / 2
blnInit = True
End If

If Target.Column = 2 Then
'Only execute this when the second column ("B") changes
lngRow = Target.Row
lngHeight = Target.Height
lngTop = Target.Top
ActiveSheet.CheckBoxes.Add(Left:=lngLeft, Top:=lngTop, Width:=lngWidth,
Height:=lngHeight).Select
With Selection
.Value = xlOn
.LinkedCell = "$A$" & CStr(lngRow)
.Display3DShading = True
.Characters.Text = ""
End With
'Change underlying "TRUE/FALSE" text in LinkedCell to White so that it's
invisible.
Range("$A$" & CStr(lngRow)).Font.ColorIndex = 2
End If

End Sub
 
Back
Top