J
John Smith
Hi,
I am creating a column of checkboxes that corresponds directly to a
list of employee names. The last checkbox is to select all the names.
Because the list of names will always vary in length I need to know
how to assign the macro to the last checkbox. Any ideas? Thanks.
James
Sub AddCBX() 'for OptBtn2
Dim WB As Workbook
Dim sh As Worksheet
Dim rng As Range
Dim rCell As Range
Dim LastRow As Long
Set WB = ThisWorkbook
Set sh = WB.Sheets("Sheet2")
sh.Select
sh.CheckBoxes.Delete
sh.Columns(1).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = sh.Range("A1", "A" & LastRow + 1)
Application.ScreenUpdating = False
For Each rCell In rng.Cells
With sh.CheckBoxes.Add(rCell.Left + 5, rCell.Top - 2, 5, 5)
.Caption = ""
.LinkedCell = rCell.Address(False, False)
End With
rCell.Font.Color = vbWhite
Next rCell
sh.Columns(1).ColumnWidth = 3.86
With sh.Cells(LastRow + 1, "B")
.Value = "Select All"
.Select
With Selection.Font
.ColorIndex = xlAutomatic
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
End With
Application.ScreenUpdating = True
End Sub
Sub AllCheck() 'to select all names
Dim myCBX As CheckBox
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End Sub
I am creating a column of checkboxes that corresponds directly to a
list of employee names. The last checkbox is to select all the names.
Because the list of names will always vary in length I need to know
how to assign the macro to the last checkbox. Any ideas? Thanks.
James
Sub AddCBX() 'for OptBtn2
Dim WB As Workbook
Dim sh As Worksheet
Dim rng As Range
Dim rCell As Range
Dim LastRow As Long
Set WB = ThisWorkbook
Set sh = WB.Sheets("Sheet2")
sh.Select
sh.CheckBoxes.Delete
sh.Columns(1).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = sh.Range("A1", "A" & LastRow + 1)
Application.ScreenUpdating = False
For Each rCell In rng.Cells
With sh.CheckBoxes.Add(rCell.Left + 5, rCell.Top - 2, 5, 5)
.Caption = ""
.LinkedCell = rCell.Address(False, False)
End With
rCell.Font.Color = vbWhite
Next rCell
sh.Columns(1).ColumnWidth = 3.86
With sh.Cells(LastRow + 1, "B")
.Value = "Select All"
.Select
With Selection.Font
.ColorIndex = xlAutomatic
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
End With
Application.ScreenUpdating = True
End Sub
Sub AllCheck() 'to select all names
Dim myCBX As CheckBox
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End Sub