Microsoft Office Forums - View Single Post - [Solved] Falling at the last hurdle with Checkboxes and Option buttons

NoSparks · 06-09-2019, 08:57 PM

Quote:

In place of the loops write 3 lines for sheets and 3 lines for optionbuttons each using the actual name.

You've completely lost me - I'm afraid that I really need some assistance with what you mean.

The old Private Sub OptionButton1_Change() would become something like this,
although I don't think VBA accepts a control name with a space in it.

Code:

Private Sub NextGen_Change()
     If NextGen.Value = True Then
        ' this replaces the loop
        Me.Controls(NextGen).BackColor = RGB(244, 244, 244)
        Me.Controls(Advanced).BackColor = RGB(244, 244, 244)
        Me.Controls(All Future).BackColor = RGB(244, 244, 244)
        Sheets("Report template - NextGen").Visible = True
        Sheets("Report template - Advanced").Visible = True
        Sheets("Report template - All Future").Visible = True
        '
        Sheets(Array("Report template - Advanced", "Report template - All Future")).Visible = False
        Sheets("Report template - NextGen").Activate
        Range("H4").Select
        SendKeys "%{down}", True
        NextGen.BackColor = RGB(128, 255, 128)
        cmdContinue.Enabled = True
     End If
End Sub