How to Create Toggle Button on Excel VBA UserForm

A User Form is a specially created dialog box that gives the user additional control and ease of use over their data submission. Numerous functions are offered by Microsoft Excel to increase our convenience. With Microsoft Excel, we can perform those tasks quickly. In this article, we will demonstrate the procedures to create a toggle button on Excel VBA Userform. As we know that the toggle button provides a setting change option for the user.

Download Practice Workbook

You can download the workbook and practice with them.

What Is Toggle Button?

ToggleButton is employed to show a button’s enabled and unchecked states. It describes a control that is used to alternate between two or more states or alternatives. When a ToggleButton is connected to a data source, it displays the data source’s present value as Yes/No, True/False, On/Off, or another pair of binary options.

A toggle button is a control that performs one function at the first click at the first click, performs one function, and, upon the second click, another one. The button switches (or toggle switches) between the two of its modes, On and Off. When the button is chosen, it might have a value of True (where it seems pushed) or False (in which it looks unpressed).

Step-by-Step Procedures to Create Toggle Button on Excel VBA UserForm

Let’s look at the procedures to create a toggle button on Excel Userform. We also incorporated different kinds of customized toggle buttons.

Step 1: Launch Excel Developer Tab

The Developer tab gives you access to Excel’s more sophisticated tools and special features. You must make sure that the Developer tab is present on the Excel Ribbon when you intend to work with VBA macros. By default, the Developer tab, which contains helpful instructions for VBA users, does not show up. To launch the Developer command let’s look at the steps down.

• Firstly, right-click on the ribbon and go to Customize the Ribbon.

• Thus, the Excel Options dialog box will appear.
• Secondly, click on Customize Ribbon and checkmark the Developer tab.
• Finally, click OK to complete the step.

Step 2: Generate Toggle Button on Excel Sheet

In this step, we will generate a toggle button in our worksheet. To do so, follow the instructions down

• To begin with, go to the Developer tab which we just enabled on our ribbon.
• Then, click on the Insert drop-down menu, under the Controls group.
• Further, choose Toggle Button from the ActiveX Controls option.

• Now, drag your mouse and place the button as per your requirement.

• To change the name of the ToggleButton, right-click on the button and go to ToggleButton Object.
• Further, click on Edit.

• Lastly, change the name, In our case, we change the toggle button name to Click Here to Show the Userform. As we want to see the toggle button on Excel VBA Userform.

Read More: How to Show Menu Bar in Excel (2 Common Cases)

Similar Readings

• [Fixed!] CTRL C Not Working in Excel
• If a Value Lies Between Two Numbers Then Return Expected Output in Excel
• How to Move Up and Down in Excel (5 Easy Methods)
• Create an Organizational Chart in Excel from a List
• How to Calculate Root Mean Square Error in Excel

Step 3: Construct Excel VBA UserForm

To create a customized interface we use the userform in Excel. Let’s construct the user form.

• In the beginning, go to the Developer tab from the ribbon.
• Secondly, click on Visual Basic from the Code category to open the Visual Basic Editor. Or press ALT+F11 to open the Visual Basic Editor.

• Thus, the Visual Basic Editor will appear.
• Thirdly, click on UserForm from the Insert drop-down menu bar.

• This will create a UserForm in your Visual Basic Editor.
• After that, 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, we add a Frame which we name Data Entry Form.
• Then, we attach some Levels; EMP ID, EMP Name, Designation, Gender, and Salary.
• Also, we include some Text Boxes in front of EMP ID, EMP Name, and Salary.
• Additionally, we append two OptionButton; one is Male other one is Female.
• Further, we add up a ComboBox in front of the Designation level.
• Finally, we include two CommandButton one is Add and the other one is Clear.

Read More: How to Mark Workbook as Final in Excel (with Easy Steps)

Step 4: Create Toggle Button on UserForm

Now, we will generate our toggle button on the user form. Let’s look at the steps below.

• Go to View on the Visual Basic Editor ribbon.
• Then, click on Toolbox.

• This will display the Toolbox dialog.
• Further, select the ToggleButton.
• Drag the mouse and place the button.

• Likewise, the Toolbox menu enables the Properties Window.
• Change the Caption of the toggle button. In our case, we name the button Minimize.

• This is the final form with a toggle button.

Similar Readings

• Descriptive Statistics – Input Range Contains Non-Numeric Data
• How to Do Descriptive Statistics in Excel (2 Suitable Methods)
• Make Trial Balance in Excel (with Easy Steps)
• How to Get Summary Statistics in Excel (7 Easy Methods)
• Make Fishbone Diagram in Excel (with Easy Steps)

Step 5: Connect Worksheet Toggle Button with UserForm

We will now link the userform with the worksheet toggle button.

• Firstly, double-click on Sheet, under Project – VBA Project.
• Then, copy and paste the VBA code shown below.

VBA Code:

Private Sub ToggleButton1_Click()
UserForm.Show False
End Sub

• Further, click on the Save button to save the macro or press CTRL+C.

• Now, go back to your worksheet and click on the toggle button (Click Here to Show the Userform) that you created in the first place.
• And, you can see that the form will appear in the sheet, which means it is now connected to the sheet’s toggle button.

Step 6: Customize Toggle Button

We can customize the user form toggle button. Suppose, we want that while we click on the toggle button the user form will minimize and the name of the toggle button will automatically change to maximize and the form itself minimize.

• Double-click on the toggle button.

• Write the 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

• Further, press the F5 key or click on the Run Sub button to run the code.

• This will open the userform in the sheet. Now, click on the Minimize button which is mainly our toggle 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 Toggle Button Color When Pressed in Excel VBA UserForm

Let’s change the color of the toggle button. For this, just follow the above instructions and change the code of the toggle button.

STEPS:

• Click the toggle button twice.
• There, type the 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

• Additionally, to run the code, hit the F5 key or click the Run Sub button.

• By doing so, the user form in the sheet will open. The Minimize button changes its color which is primarily our toggle button, and should now be clicked.

• If you carefully examine the button, it will immediately modify its name and color and minimize the form.
• By selecting the Maximize button, the form will display correctly.

Read More: How to Color Code Cells in Excel (3 Efficient Methods)

Things to Remember

• 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.

Conclusion

The above procedures will assist you to Create Toggle Button on Excel VBA Userform. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!

Related Articles

• Show Ruler in Excel (with Simple Steps)
• How to Display Document Properties in Excel (3 Simple Methods)
• [Fixed!] Excel Ruler Not Showing (With Easy Solution)
• How to Show All Text in an Excel Cell (2 Easy Ways)
• Search Text in Multiple Excel Files (2 Easy Ways)
• How to Show Coordinates in Excel Graph (2 Easy Ways)
• [Fixed!] Excel Links Not Working Unless Source Workbook Is Open