View Single Post
 
Old 08-24-2015, 06:46 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

If you have a lot of check boxes to put on your sheet, maybe you can make use of this macro I came across in the forums long time ago.
Play around with it in a blank workbook, it can be handy.
Code:
 Private Sub Insert_Checkboxes()
     Dim myCell As Range
     Dim myRng As Range
     Dim CBX As CheckBox

     With ActiveSheet
         'delete existing checkboxes from sheet, links are not cleared.
         .CheckBoxes.Delete
         'Exit Sub   '<~~~~~ uncomment to delete checkboxes only. Linked cells are NOT cleared.
         Set myRng = .Range("A2:A11")    '<~~~~~ 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"     'or whatever you want
             CBX.Value = xlOff    'initial value unchecked
             CBX.LinkedCell = .Offset(0, 1).Address     '<~~~~~ enter offset to linked cell
         End With
     Next myCell
Application.ScreenUpdating = True
End Sub
Reply With Quote