VBA Express : Excel - Use a Command Button as Toggle Button

Excel

Use a Command Button as Toggle Button

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

On running the code the command buttons size and caption alternates.

Discussion:

It's not always desirable to use a standard toggle button, this code uses a single command button to demonstrate how to use an If-Then-Else statement to alternate between two different states. Its' use is not restricted to command buttons, the same technique can also be used with most other controls.

Code:

instructions for use

			 '************************************
 '<< CODE FOR THE ThisWorkbook MODULE >>
 
Option Explicit 
 
Private Sub Workbook_Open() 
    With Sheet1.CommandButton1 
        .Height = 20 
        .Width = 60 
        .Left = 150 
        .Top = 80 
        .Caption = "Click Me" 
    End With 
End Sub 
 '************************************
 
 
 
 '************************************
 '<< CODE FOR THE SHEET1 MODULE >>
Option Explicit 
 
Private Sub CommandButton1_Click() 
    With CommandButton1 
        .Height = 20 
        .Width = 60 
        If .Caption = "Click Me" Then 
            .Width = 180 
            .Font.Bold = True 
            .Caption = "Ohh YES, I Like That! - Do It Again!!" 
        Else 
            .Width = 60 
            .Font.Bold = False 
            .Caption = "Click Me" 
        End If 
    End With 
End Sub 
 '************************************

How to use:

Open a new Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Tools/Project Explorer
Select the ThisWorkbook module
Copy and paste the 'ThisWorkbook' code into this Module
Select the Sheet1 module
Copy and paste the 'Sheet1' code into this Module
Now select File/Close and Return To Microsoft Excel
Select View/Toolbars/Control Toolbox and click Command Button
Place the mouse pointer on the worksheet and draw it down & to the right
Close the Control Toolbox (click the X)
Save and close the workbook...

Test the code:

Open the workbook and click the command button - repeat

Sample File:

ToggleItFun.zip 9.48KB

Approved by mdmackillop

This entry has been viewed 236 times.