Make a simple button behave like a toggle button?

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
Hi, everybody

Suppose I have a simple button (not a toggle button).

I want it to run the macro "Macro1" when I click on it the first time, and run "Macro2" when I click it a second time.

If I click it a third time, it will run "Macro1" again...

I want something simple, because I will later need to translate the macros into VB .NET.

How can this be done?

Thanks.

Leon
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Re: How can we make a simple button behave like a toggle button?

Just change the colour of the button ie.if the button is green, run only macro1 and turn it red. If the button is red, run only macro2 and change it to green. HTH. Dave
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Look into static variables. They hold their value even when the macro has finished running. You could potentially set a static boolean variable and then determine which macro to run with something along the lines of

Code:
If togglevar = True Then
    Macro1
Else
    Macro2
End if
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Hi,

Do not bother, unless there is a better solution. I found the solution:

Sub Toggle()
Code:
Public Flag1 As String

Code:
If Flag1 = "A" Or Flag1 = "" Then
Call Macro1
Flag1 = "B"


Thanks
Leon




ElseIf Flag1 = "B" Then
Call Macro2
Flag1 = "A"


End If


End Sub
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Hi, Tim_Excel_

Thanks a lot for your reply.

Our answers crossed, and they correspond. I will try your idea of Static variable instead of Public.

Best Regards,
Leon


====

ADDED:
Static works fine! Thanks
 
Last edited:
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Another simple idea is to change the caption for the button (make the caption meaningful so the user knows what pressing the button means) and key which macro to run depending on the caption displayed on the button.
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Hi, Rick

That's a great idea!

My button works correctly, but the caption stays fixed. It would be helpful if it changes also.

Could you give me an idea how you can accomplish that?

Thanks
Leon
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Hi, NdNoviceHlp

Thanks for your reply.

How can you do that?

Leon
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Rick's would be something like this...
Code:
If UserForm1.CommandButton1.Caption = "Macro1" Then
'code for macro1
UserForm1.CommandButton1.Caption = "Macro2"
Else
'code for macro2
UserForm1.CommandButton1.Caption = "Macro1"
End If
Change colours...
Code:
If UserForm1.CommandButton1.BackColor = RGB(255, 0, 0) Then 'red
'code for macro1
UserForm1.CommandButton1.BackColor = RGB(0, 128, 64) 'green
Else
'code for macro2
UserForm1.CommandButton1.BackColor = RGB(255, 0, 0)
End If
U will need to adjust the userform name and command button name. Also to start, U will need to place some ode in the initialize event. ie either of the following based on what U use...
Code:
Private Sub UserForm_Initialize()
UserForm1.CommandButton1.Caption = "Macro1"
UserForm1.CommandButton1.BackColor = RGB(255, 0, 0)
End Sub
HTH. Dave
 
Upvote 0
Re: How can we make a simple button behave like a toggle button?

Hi, Rick

That's a great idea!

My button works correctly, but the caption stays fixed. It would be helpful if it changes also.

Could you give me an idea how you can accomplish that?
What kind of button is it... one found on a UserForm or, if on a worksheet, is it a Forms button or an ActiveX button?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top