Visual Basic for Applications/CommandButton Toggle

Summary

This VBA code module is made for Microsoft Excel but is easily adapted for use in other Office applications that can run VBA with user forms:

• Two improved CommandButton_Click() procedures are provided. They show better emphasis when buttons are prssed. CmmandButton1_Click() has a toggle action, for example to set either one of two possible modes, and CommandButton2_Click() performs the usual task of one function. In each case:
  • The user-set captions change in transition to reflect the current state of the buttons, for example Running etc. This can be of interest in the toggle states or when a procedure takes a long time to run.
  • The button size is swelled about its center. This avoids enlargement from a fixed top-left point. The amount of the increase can be set within the procedure.
  • The background and font color of the buttons are set in code, so are easily modified. Separate colors can be used for the two states.
  • The Me.Repaint lines ensure that the button formats update immediately. If they are not present the procedures will start, and perhaps end, before that was done. Without Me.Repaint, but when DoEvents exists in the procedure-to-run, the repaint might still appear to work normally, until a procedure is run that does not require DoEvents. For those who intend to study this point, it is shown up best in the CommandButton2_Click() procedure.

The Code Module

Copy the UserForm_Initialize(), CommandButton1_Click(), and CommandButton2_Click() procedures into the UserForm module of an Excel project. This can be achieved by first inserting a form called UserForm1, with one CommandButton called CommandButton1 and another CommandButton2. Double-click within the form in design mode to access its module. The Workbook_Open() procedure goes into the ThisWorkbook module. Then, save the workbook, and run Workbook_Open() (or re-open the workbook) to test the buttons on the form.

Modifications

• 20 Jan 2019, added the previously omitted Me.Repaint code lines

Private Sub Workbook_Open()
    'run this to show form
    
    Load UserForm1
    UserForm1.Show

End Sub


Option Explicit

Private Sub UserForm_Initialize()
        
    With CommandButton1
        .Height = 50
        .Width = 50
        .Caption = "Turn ON"
        .BackColor = RGB(255, 205, 183)
    End With
    With CommandButton2
        .Height = 50
        .Width = 50
        .Caption = "Turn ON"
        .BackColor = RGB(255, 205, 183)
    End With

End Sub

Private Sub CommandButton1_Click()
    'TOGGLES caption, color and size-about-center
    'of a typical CommandButton control, between
    'say, two stable modes of working.
            
    Dim nInc As Integer, n As Long
    
    'set size increase (say 0 to 10)
    nInc = 8
        
    With CommandButton1
        'run the OFF code
        If .Caption = "Turn OFF" Then
            .Width = .Width - nInc
            .Height = .Height - nInc
            .Caption = "Turn ON"
            .Left = .Left + nInc / 2
            .Top = .Top + nInc / 2
            .BackColor = RGB(255, 205, 183)
            .ForeColor = RGB(0, 0, 0)
             Me.Repaint 'redraw form
            
            'add procedure here for the OFF state
            '(simulated here with a short delay)
            For n = 1 To 100000
                DoEvents 'yield as required
            Next
        
        Else 'run the ON code
            .Width = .Width + nInc
            .Height = .Height + nInc
            .Caption = "Turn OFF"
            .Left = .Left - nInc / 2
            .Top = .Top - nInc / 2
            .BackColor = RGB(255, 217, 183)
            .ForeColor = RGB(0, 0, 0)
            Me.Repaint 'redraw form
            
            'add procedure here for the ON state
            '(simulated here with a short delay)
            For n = 1 To 100000
                DoEvents 'yield as required
            Next
        
        End If
    End With

End Sub

Private Sub CommandButton2_Click()
    'Changes color and size-about-center
    'of a typical CommandButton control,
    'holds formats during process
    'and restores all on exit.
                
    Dim nInc As Integer, n As Long
    
    'set size increase (say 0 to 10)
    nInc = 8
    
    'detect OFF state
    With CommandButton2
        If .Caption = "Turn ON" Then
            .Width = .Width + nInc
            .Height = .Height + nInc
            .Caption = "Running"
            .Left = .Left - nInc / 2
            .Top = .Top - nInc / 2
            .BackColor = RGB(255, 217, 183)
            .ForeColor = RGB(0, 0, 0)
        End If
    End With
    Me.Repaint 'redraw form
    
            'add procedure here for the ON state
            '(simulated here with a short delay)
            For n = 1 To 100000
                DoEvents 'yield as required
            Next
    
    'restore button just before exit
    With CommandButton2
        If .Caption = "Running" Then
            .Width = .Width - nInc
            .Height = .Height - nInc
            .Caption = "Turn ON"
            .Left = .Left + nInc / 2
            .Top = .Top + nInc / 2
            .BackColor = RGB(255, 205, 183)
            .ForeColor = RGB(0, 0, 0)
        End If
    End With
    Me.Repaint 'redraw form

End Sub