Excel

Execute different line of code with second click of command button

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Simon Lloyd

Description:

On first click of a command button run one section of code, on second click run second section. 

Discussion:

Ever wanted to use the same command button to run some code after its first function has finished? With the following very simple, easily expandable code you could theoretically run a new section of code with every click of the button! Handy for keeping the amount of buttons down on a worksheet or userform. 

Code:

instructions for use 

			
Option Explicit 
Public s As Integer ''''This is a publicly declared Integer i.e the value of s can be called upon elsewhere
Private Sub CommandButton1_Click() 
     
    If s = 0 Then 
        MsgBox "Hi" '''Substitute this for first section of code to run
        CommandButton1.Caption = "Ready to run second portion" 
        s = 1 
    ElseIf s = 1 Then 
        MsgBox "Bye" '''''substitute this for second section
        s = 0 
        CommandButton1.Caption = "Ready to run initial portion" 
    End If 
End Sub

How to use:

  1. Add the code above to your CommandButtonx_Click, you can extend the code by following on with the ElseIf and change s to equal the next integer.
 

Test the code:

  1. Create a userform with a command button or add a command button to a worksheet
  2. In design mode double click then button and the VBE will open up at the CommandButton Click
  3. Copy and paste the code above in to it
  4. Exit design mode
  5. Call userform and click button or click button on worksheet - see the message
  6. Click the button again see alternate message
  7. Cick the button again see the first message
 

Sample File:

Two Click Button.zip 6.6KB 

Approved by mdmackillop


This entry has been viewed 284 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express