Handling dozens of checkbox events in a Word document

I have a word document which has a lot of active x checkboxes on it and I have just been told the way it has been coded and done could be improved upon a lot, so I am asking for a bit of help.

To quickly explain it, the checkboxes are tied to a "completion overview" at the bottom of the document.

The code on the checkboxes basically when checked, changes a active x label at the bottom to green from red, and changes the text "outstanding" to "completed".

There are also buttons on my word doc which hide certain sections of the document, so when this is done, all checkboxes hidden with it need to have been ticked as they are hidden.

If there are more than one checkbox under a certain header, they all need to be ticked before the list at the bottom will change to green and completed.
Here is some code for a 3 checkbox bit, I am sure it can be vastly improved on if not completely changed!

Private Sub UpdateWorkflowSection()
If (WorkflowHasBeenSetupUpCheckBox.Value = True And     RuleSetupCheckBox.Value = True And AddedNewUserCheckBox.Value = True) Then
        Section4Complete.Caption = "Complete": Section4Complete.BackColor = RGB(0, 255, 0): WokflowBy.Caption = UpgradeTechnic.Text
Else
        Section4Complete.Caption = "Outstanding":     Section4Complete.BackColor = RGB(255, 0, 0): WokflowBy.Caption = ""
    End If
End Sub

Here is a dump of all my VBA on this document:

```
Private Sub TestingStageHyperLink_Click()
ActiveDocument.FollowHyperlink "#TESTING STAGE"
End Sub

Private Sub CompletionOverviewHyperLink_Click()
ActiveDocument.FollowHyperlink "#Completion Overview "
End Sub

Private Sub Document_Open()
UpgradeTechnic.List = Array("Tom B", "Liam", "Mat")
End Sub

Private Sub AllDocumentsPostedCheckbox_Click()
If (AllDocumentsPostedCheckbox.Value = True) Then
Section8Complete.Caption = "Complete": Section8Complete.BackColor = RGB(0, 255, 0): DocInputBy.Caption = UpgradeTechnic.Text
Else

First of all, kudos for naming all these controls!

You have a lot of duplication going on; extract functionality into more specialized functions/procedures. For example, this:

If (AllDocumentsPostedCheckbox.Value = True) Then
    Section8Complete.Caption = "Complete": Section8Complete.BackColor = RGB(0, 255, 0): DocInputBy.Caption = UpgradeTechnic.Text
Else
    Section8Complete.Caption = "Outstanding": Section8Complete.BackColor = RGB(255, 0, 0): DocInputBy.Caption = ""
End If

Can be generalized/abstracted to:

SetCompletionStatus AllDocumentsPostedCheckbox.Value, Section8Complete

Where SetCompletionStatus could look like this - note the use of VBA.ColorConstants here:

Private Sub SetCompletionStatus(ByVal isCompleted As Boolean, ByVal section As Object)

    Const CompletedColor As Long = VBA.ColorConstants.vbGreen
    Const OutstandingColor As Long = VBA.ColorConstants.vbRed

    section.Caption = IIf(isCompleted, "Complete", "Outstanding")
    section.BackColor = IIf(isCompleted, CompletedColor, OutstandingColor)
    DocInputBy.Caption = IIf(isCompleted, UpgradeTechnic.Text, vbNullString)

End Sub

And all of a sudden every single checkbox handler becomes a one-liner:

Private Sub ClientTestingCheckBox_Click()
    SetCompletionStatus ClientTestingCheckBox.Value, Section11Complete
End Sub

I notice your code contains a lot of instructions separators (:) - as @FreeMan noted, these are doing a great job with making the code harder to read and maintain. It's much easier to see what's going on when a given line of code contains a single instruction.

These two procedures alone are responsible for much of the horizontal scrolling:

```
Private Sub V4ToV6Button_Click()
ActiveDocument.Sections(2).Range.Font.Hidden = True: ActiveDocument.Sections(4).Range.Font.Hidden = True: ActiveDocument.Sections(6).Range.Font.Hidden = True: ActiveDocument.Sections(8).Range.Font.Hidden = True: ActiveDocument.Sections(10).Range.Font.Hidden = True: Section15Complete.Caption = "": Section15Complete.BackColor = RGB(255, 255, 255): ActiveDocument.Tables(1).Rows(22).SetHeight 1, wdRowHeightExactly: SQLScriptCheckbox.Value = True: SQLScriptCheckbox.Height = 1: SQLScriptCheckbox.Width = 1: SQLScriptCheckbox.Enabled = False: RestoreEmailScriptCheckBox.Value = True: RestoreEmailScriptCheckBox.Height = 1: RestoreEmailScriptCheckBox.Width = 1: RestoreEmailScriptCheckBox.Enabled = False: SQLCleanScriptCheckBox.Value = True: SQLCleanScriptCheckBox.Height = 1: SQLCleanScriptCheckBox.Width = 1: SQLCleanScriptCheckBox.Enabled = False
SandboxJobHasBeenSetUpCheckBox.Value = True: SandboxJobHasBeenSetUpCheckBox.Width = 1: SandboxJobHasBeenSetUpCheckBox.Height = 1: SandboxJobHasBeenSetUpCheckBox.Enabled = False: LedgerListComplete.Caption = "N/A": LedgerListComplete.BackColor = RGB(139, 0, 139): BankBalanceComplete.Caption = "N/A": BankBalanceComplete.BackColor = RGB(139, 0, 139): BankReconcComplete.Caption = "N/A": BankReconcComplete.BackColor = RGB(139, 0, 139): BudgetComplete.Caption = "N/A": BudgetComplete.BackColor = RGB(139, 0, 139): AllocationComplete.Caption = "N/A": AllocationComplete.BackColor = RGB(139, 0, 139)
TrialBalanceComplete.Caption = "N/A": TrialBalanceComplete.BackColor = RGB(139, 0, 139): REQSection.Caption = "N/A": REQSection.BackColor = RGB(139, 0, 139)
End Sub

Private Sub V2ToV6Button_Click()

ActiveDocument.Sections(2).Range.Font.Hidden = False: ActiveDocument.Sections(4).Range.Font.Hidden = False: ActiveDocument.Sections(6).Range.Font.Hidden = False: ActiveDocument.Sections(8).Range.Font.Hidden = False: ActiveDocument.Sections(10).Range.Font.Hidden = False: Section15Complete.Caption = "Outstanding": Section15Complete.BackColor = RGB(255, 0, 0): ActiveDocument.Tables(1).Rows(22).SetHeight Auto, wdRowHeightAuto: SQLScriptCheckbox.Value = False: SQLScriptCheckbox.Width = 151: SQLScriptCheckbox.Height = 42.75: SQLScriptCheckbox.Enabled = True: RestoreEmailScriptCheckBox.Value = False: RestoreEmailScriptCheckBox.Width = 179.75: RestoreEmailScriptCheckBox.Height = 20: RestoreEmailScriptCheckBox.Enabled = True: SQLCleanScriptCheckBox.Value = False: SQLCleanScriptCheckBox.Width = 139.85: SQLCleanScriptCheckBox.Height = 22.85: SQLCleanScriptCheckBox.Enabled = True:
SandboxJobHasBeenSetUpCheckBox.Value = False: SandboxJobHasBeenSetUpCheckBox.Width = 272.25: SandboxJobHasBeenSetUpCheckBox.Height = 22.85: SandboxJobHasBeenSetUpCheckBox.Enabled = True: LedgerListComplete.Caption = "Outstanding": LedgerListComplete.BackColor = RGB(255, 0, 0): BankBalanceComplete.Caption = "Outstanding": BankBalanceComplete.BackColor = RGB(255, 0, 0): BankReconcComplete.Caption = "Outstanding": BankReconcComplete.BackColor = RGB(255, 0, 0): BudgetComplete.Caption = "Outstanding": BudgetComplete.BackColor = RGB(255, 0, 0): AllocationComplete.Caption = "Outstanding"
AllocationComplete.BackColor = RGB(255, 0, 0): TrialBalanceComplete.Caption =

StackExchange Code Review Q#144785, answer score: 2