I need to dynamically create a sub for CB's 1 to ??. I have a sub to create the boxes, I want to make a click sub for each box to call a handler with reference to the cell position the check box is in.
I know I have read a similar solution, but after searching for longer than is practical, I'll ask what's the magic words to create a sub.
Sub Add_CheckBox(rw)
Const xCheckBoxHeight = 12
Const xCheckBoxWidth = 12
Dim r As Range
Dim xHeight As Double
Dim xLeft As Double
Dim xTop As Double
Dim xWidth As Double
Dim cl As Integer
For cl = 9 To 17 Step 2
Set r = Range(Cells(rw, cl).Address)
'Get the dimensions of the active cell
xHeight = r.Height
xLeft = r.Left
xTop = r.Top
xWidth = r.Width
'Calculate starting point for the new 'Active X Check Box'
xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
xTop = xTop + (xHeight - xCheckBoxHeight) / 2#
'Add the 'Active X' Check Box
'Link the 'Check Box' to the underlying Cell
With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=xLeft, _
Top:=xTop, _
Width:=xCheckBoxWidth, _
Height:=xCheckBoxHeight)
.Object.Caption = ""
.LinkedCell = r.Address
.Object.Value = False
'ADD CODE HERE TO CREATE A ON CLICK HANDLER
End With
'Hide the contents of the Underlying Cell
r.NumberFormat = ";;;"
Next cl
End Sub
Thanks in advance.
Bookmarks