How to Create Toggle Button on Excel VBA UserForm

Step 1 – Launch the Excel Developer Tab

If you have the Developer tab on your ribbon, you can skip this step.

• Right-click on the ribbon and go to Customize the Ribbon.

• The Excel Options dialog box will appear. Click on Customize Ribbon and check the Developer option.
• Click OK to complete the step.

Step 2 – Generate a Toggle Button on the Excel Sheet

• Go to the Developer tab.
• Click on the Insert drop-down menu under the Controls group.
• Choose a Toggle Button from the ActiveX Controls block.

• Click and drag to place the button into the sheet.

• Right-click on the button and go to ToggleButton Object.
• Click on Edit.

• Change the name. We changed the toggle button’s name to Click Here to Show the Userform.

Step 3 – Construct an Excel VBA UserForm

• Go to the Developer tab from the ribbon.
• Click on Visual Basic from the Code category to open the Visual Basic Editor, or press Alt + F11.

• The Visual Basic Editor will appear.
• Click on UserForm from the Insert drop-down menu bar.

• This will create a UserForm in your Visual Basic Editor.
• Run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

• This will open the user form window in your workbook.

• To construct the form, add a Frame named Data Entry Form.
• Attach some Levels; EMP ID, EMP Name, Designation, Gender, and Salary.
• Include some Text Boxes in front of EMP ID, EMP Name, and Salary.
• Append two OptionButtons, Male and Female.
• Add a ComboBox in front of the Designation level.
• Include two CommandButtons, Add and Clear.
• Look at the picture below to see how it should look.

Step 4 – Create the Toggle Button on the UserForm

• Go to View on the Visual Basic Editor ribbon.
• Click on Toolbox.

• This will display the Toolbox dialog.
• Select the ToggleButton.
• Click inside the user form an drag to draw the button.

• The Toolbox menu goes to the Properties Window.
• Change the Caption of the toggle button. We named the button Minimize.

• This is the final form with a toggle button.

Step 5 – Connect the Worksheet Toggle Button with the UserForm

• Double-click on Sheet, under Project – VBA Project.
• Copy and paste the VBA code shown below.

VBA Code:

Private Sub ToggleButton1_Click()
UserForm.Show False
End Sub

• Click on the Save button to save the macro or press Ctrl + S.

• Go back to your worksheet and click on the toggle button.
• The form will appear in the sheet, which means it is now connected to the sheet’s toggle button.

Step 6 – Customize the Toggle Button

• Double-click on the toggle button.

• Use the following code there.

VBA Code:

Private Sub ToggleButton1_Click()
    If Me.ToggleButton1.Value = True Then
        Me.Height = 50
        Me.Width = 50
        Me.Top = 450
        Me.Left = 0
        Me.ToggleButton1.Left = 0
        Me.ToggleButton1.Top = 0
        Me.ToggleButton1.Caption = "Maximize"
    Else
        Me.Height = 288
        Me.Width = 308
        Me.Top = 150
        Me.Left = 400
        Me.ToggleButton1.Left = 222
        Me.ToggleButton1.Top = 6
        Me.ToggleButton1.Caption = "Minimize"
    End If
End Sub

• Press the F5 key or click on the Run Sub button to run the code.

• This will open the user form in the sheet. Click on the Minimize button.

• This will minimize the form and if you properly look at the button, it changes its name automatically.
• You can click on the Maximize button and show the form properly.

How to Change the Toggle Button Color When Pressed in an Excel VBA UserForm

Let’s change the color of the toggle button.

STEPS:

• Click the toggle button twice.
• Use the following code.

VBA Code:

Private Sub ToggleButton1_Click()
    If Me.ToggleButton1.Value = True Then
        Me.Height = 50
        Me.Width = 50
        Me.Top = 450
        Me.Left = 0
        Me.ToggleButton1.Left = 0
        Me.ToggleButton1.Top = 0
        Me.ToggleButton1.Caption = "Maximize"
    Else
        Me.Height = 288
        Me.Width = 308
        Me.Top = 150
        Me.Left = 400
        Me.ToggleButton1.Left = 222
        Me.ToggleButton1.Top = 6
        Me.ToggleButton1.Caption = "Minimize"
    End If
   If ToggleButton1.Value = True Then
   If Cells(5, 2).Value > Cells(5, 3).Value Then
        With Me.ToggleButton1
        Me.ToggleButton1.Caption = "Toggle Button"
        .BackColor = IIf(.Value, vbRed, vbButtonFace)
        End With
        ElseIf Cells(5, 2).Value = Cells(5, 3).Value Then
        With Me.ToggleButton1
        Me.ToggleButton1.Caption = "Maximize"
        .BackColor = IIf(.Value, vbGreen, vbButtonFace)
        End With
        ElseIf Cells(5, 2).Value < Cells(5, 3).Value Then
        With Me.ToggleButton1
            Me.ToggleButton1.Caption = "Toggle Button"
        .BackColor = IIf(.Value, vbYellow, vbButtonFace)
        End With
   End If
   End If
End Sub

• Hit the F5 key or click the Run Sub button.

• The user form in the sheet will open. The Minimize button changes its color.

• By selecting the Maximize button, the form will display correctly.

Things to Remember

• A ToggleButton resembles both a CommandButton and a CheckBox which has toggle capability and clickable and similar appearance till clicked.
• A CheckBox may be selected or deselected to toggle On and Off. To identify an on/off (select/deselect) status, ToggleButton employs a single button, whereas OptionButtons require two independent buttons. The majority of people, however, find it simpler to utilize CheckBox or OptionButtons.
• If the TripleState attribute is set to True, ToggleButton can also have a Null value (i.e., neither selected or unselected), in which case it will look darkened.

Download the Practice Workbook

Related Articles

• Excel VBA: Show Userform in Full Screen
• Excel VBA: UserForm Image from Worksheet
• How to Use Excel UserForm as Date Picker
• How to Use VBA to Get Value from Userform Textbox in Excel
• Excel VBA to Format Textbox Number with UserForm