Click a macro button twice

[pincivma]

Hi again
I'm pretty sure that this is doable but I'm having a hard time with the macro. I'll make it simple for you Excel gurus out there. Say I have only one button that executes a macro. Say the macro puts a one in Cell A1. Then when I click on the same macro button, it puts in a zero in cell A1. My macro is very complex but if someone out there can give me a hint, I can probably figure it out myself, MAYBE.

 

[Computerman]

Pincivma,
Try this in your button click event:

If Sheet1.Cells(1,1).value=”1” then
    Sheet1.Cells(1,1).value=0” 
Else
    Sheet1.Cells(1,1).value=”1”
End if

Replace the ‘sheet1’ with the name of your sheet.
Computerman

 

[B___P]

Hi pincivma,
I guess you put 0 or 1 in cell A1 just as an example of macro with an alternating behavior between calls

as a starting point

Sub MyButton()
Static a As Long
a = a + 1
If a Mod 2 = 0 Then
    Cells(1, 1).Value = 0
    'or other code
Else
    Cells(1, 1).Value = 1
    'or other code
End If
End Sub

Hope this helps

 

[pincivma]

Hi pincivma,
I guess you put 0 or 1 in cell A1 just as an example of macro with an alternating behavior between calls

as a starting point

Sub MyButton()
Static a As Long
a = a + 1
If a Mod 2 = 0 Then
    Cells(1, 1).Value = 0
    'or other code
Else
    Cells(1, 1).Value = 1
    'or other code
End If
End Sub

Hope this helps

Thanks. I will give it a try and see how it works out in my complicated macro

 

[jolivanes]

Sub Maybe_A()
    [A1] = IIf([A1] = 1, 0, 1)
End Sub

Sub Maybe_B()    'Thanks To Rick Rothstein
    [A1] = 1 - [A1]
End Sub

 

[pincivma]

Hi Jolivanes

Thanks for the short macros. They both work.

 

[mikerickson]

I like to alter the caption of a button to toggle (or multiple) its actions.
That way the user knows what it will do and its state will be preserved between runnings of Excel.
With a Form's Button the code would look like

Sub Button1_Click()
    With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
        Select Case .Text
            Case "One Thing"
                Rem do one thing
                .Text = "Thing Two"
            Case "Thing Two"
                Rem do thing two
                .Text = "Third"
            Case Else
                Rem do a third thing
                .Text = "One Thing"
        End Select
    End With
End Sub

 

[pincivma]

Thanks Mike
I will also give your macro a try.

 

[pincivma]

I like to alter the caption of a button to toggle (or multiple) its actions.
That way the user knows what it will do and its state will be preserved between runnings of Excel.
With a Form's Button the code would look like

Sub Button1_Click()
    With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
        Select Case .Text
            Case "One Thing"
                Rem do one thing
                .Text = "Thing Two"
            Case "Thing Two"
                Rem do thing two
                .Text = "Third"
            Case Else
                Rem do a third thing
                .Text = "One Thing"
        End Select
    End With
End Sub

Hi Mike

I tested out your button macro and it worked great. I tried only 2 cases instead of your 3 but I could not get the macro to work properly. Can you resend your macro with only 2 cases? I want to see where I went wrong. Thanks.

 

[mikerickson]

The key is to list one specific case and one Case Else so that any oddityie are swept up in the Case Else

Sub Button1_Click()
    With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
        Select Case .Text
            Case "One Thing"
                Rem do one thing
                .Text = "Thing Two"
            Case Else
                Rem do the second thing
                .Text = "One Thing"
        End Select
    End With
End Sub