Improve 33 checkbox code subs to few? (Checkbox for auto-date in bookmarks)

Question

:) Im new to VBA! I have a working code for inserting date where i have a bookmark when using a checkbox (ActiveX). Problem is i have 33 checkboxes (I actually wish for 33x2. one for yes and one for no). So i ended up with 33 Subs and 33 bookmarks. I bet this code can be more efficient braking it down to just a few subs. Annyone has anny idea if it can be done? The code under is the first of 33 repeating subs where Sub and bookmark name is agi1, agi2 agi3.....

Private Sub agi1_Click()

Dim rngFormat As Range
 Set rngFormat = ActiveDocument.Range( _
 Start:=ActiveDocument.Bookmarks("agi1").Range.Start, _
 End:=ActiveDocument.Bookmarks("agi1").Range.End)
 With rngFormat
 .Font.Size = 8
 End With

   Dim v
   Dim BMRange As Range
   v = ThisDocument.agi1.Value

'Sjekke om boks er sjekket eller ikke
   If v = True Then

'Sett inn dato i bokmerke
Set BMRange = ActiveDocument.Bookmarks("agi1").Range
With Selection.Font
 .Size = 9
End With
BMRange.Text = (Format(Date, "dd.mm.yyyy"))

Else

'Erstatte dato med tom tekst hvis boks ikke er sjekket
Set BMRange = ActiveDocument.Bookmarks("agi1").Range
BMRange.Text = " "

End If
'Sett inn bokmerke på nytt
ActiveDocument.Bookmarks.Add "agi1", BMRange

End Sub

Answer 1

ActiveX controls always register their event handlers like so:

Private Sub NameOfTheControl_NameOfTheEvent({args})

If you rename the handler, the control stops working - because the name of the handler must be formed as above, with an underscore separating the name of the control and the name of the handled event.

So if your controls must exist at compile-time, there's no way around it: for 33 controls you need 33 handlers.

That doesn't mean you need that huge procedure repeated 33 times!

Extract a procedure. Select the entire body of that handler, cut it.

Now make a new procedure prototype:

Private Sub HandleCheckBoxClick(ByVal controlName As String)

End Sub

And paste the body in there. Then replace all the places you have a hard-coded "agi1" with a reference to this controlName parameter:

Dim rngFormat As Range
Set rngFormat = ActiveDocument.Range( _
    Start:=ActiveDocument.Bookmarks(controlName).Range.Start, _
    End:=ActiveDocument.Bookmarks(controlName).Range.End)
With rngFormat
    .Font.Size = 8
End With

'...

The places where you're referring to the control using its programmatic name will be a bit harder:

v = ThisDocument.agi1.Value

You can get the MSForms.CheckBox control through the ThisDocument.InlineShapes collection, but that won't let you find a checkbox by its name, so you need a function that can do it for you:

Private Function FindCheckBoxByName(ByVal controlName As String) As MSForms.CheckBox
    Dim sh As InlineShape
    For Each sh In ThisDocument.InlineShapes
        If TypeOf sh.OLEFormat.Object Is MSForms.CheckBox Then
            If sh.OLEFormat.Object.Name = controlName Then
                'return the MSForms control:
                Set FindControlByName = sh.OLEFormat.Object
            End If
        End If
    Next

And now you can do this:

Dim cb As MSForms.ChecBox
Set cb = FindCheckBoxByName(controlName)
If cb Is Nothing Then
    MsgBox "No ActiveX CheckBox control named '" & controlName & "' was found in ThisDocument."
    Exit Sub
End If
v = cb.Value

Once all references to the ActiveX control are parameterized, your 33 handlers can now look like this:

Private Sub agi1_Click()
    HandleCheckBoxClick "agi1"
End Sub

Private Sub agi2_Click()
    HandleCheckBoxClick "agi2"
End Sub

'...

Private Sub agi33_Click()
    HandleCheckBoxClick "agi33"
End Sub

Alternatively, you could have the checkboxes created at run-time, and then have their Click event handled in a dedicated class module, but that's a little bit more involved ;-)