Make a simple button behave like a toggle button?

[leonlai]

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

 

[NdNoviceHlp]

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

 

[Tim_Excel_]

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

If togglevar = True Then
    Macro1
Else
    Macro2
End if

 

[leonlai]

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()

Public Flag1 As String

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


Thanks
Leon




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


End If


End Sub

 

[leonlai]

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

 

[Rick Rothstein]

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.

 

[leonlai]

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

 

[leonlai]

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

 

[NdNoviceHlp]

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

Rick's would be something like this...

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

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

Private Sub UserForm_Initialize()
UserForm1.CommandButton1.Caption = "Macro1"
UserForm1.CommandButton1.BackColor = RGB(255, 0, 0)
End Sub

HTH. Dave

 

[Rick Rothstein]

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?