Using the Same Button to reverse macro
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Using the Same Button to reverse macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Here's my code attached to my button that
    sets up sheet , freezing the part number
    column and header:

    Application.Goto Reference:="R1C1"
    ActiveCell.Offset(14, 6).Range("A1").Select
    ActiveWindow.SmallScroll ToRight:=6
    ActiveWindow.SmallScroll Down:=8
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWindow.FreezePanes = True

    Is there a way of using the same button to
    reverse the instructions? (ie Click to set,
    click to reset, etc, etc. Kinda of like an
    on/off series). Or do I need to use another button?

    Thanks,
    Mike

    Mike

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Previously I have used a rather unsophisticated way to do this using an If statement in the code and a value in a cell on the worksheet,eg
    If Range("IV1").Value = 1 Then (first option)
    end it by changing the value to 0
    Else
    If Range("IV1").Value = 0 (second option)
    end it by changing the value to 1
    Use data validation on IV1 so that it only accepts 1 or 0

    Hope this helps
    Derek
    ps Sometimes I use the cell underneath my macro button for the 1 or 0 so users don't see it
    [ This Message was edited by: Derek on 2002-02-28 06:01 ]

    [ This Message was edited by: Derek on 2002-02-28 06:05 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could change the button's caption and use an If...Then as follows: -

    If CommandButton1.Caption = "Forward" Then
    CommandButton1.Caption = "Reverse"
    Application.Goto Reference:="R1C1"
    ActiveCell.Offset(14, 6).Range("A1").Select
    ActiveWindow.SmallScroll ToRight:=6
    ActiveWindow.SmallScroll Down:=8
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWindow.FreezePanes = True

    Else:
    CommandButton1.Caption = "Forward"
    ActiveWindow.FreezePanes = False
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWindow.SmallScroll Up:=8
    ActiveWindow.SmallScroll ToLeft:=6
    ActiveCell.Offset(14, 6).Range("A1").Select
    Application.Goto Reference:="R1C1"

    End If

    You might need to add an ActiveSheet. before the CommandButton1.Caption.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So there's really no way of pushing the button again to reverse things?

    I saw the Up and Down Arrow Toggle button,
    which I was hoping was split in such a way you could assign 2 separate macros, but I should be so lucky .

    Thanks for your help.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-28 06:10, Zac wrote:
    So there's really no way of pushing the button again to reverse things?
    Yup, that's what my code does (honest ).

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code on a simple macro button seems to reverse things every time you click it.

    If Range("IV1").Value = 0 Then
    Application.Goto Reference:="R1C1"
    ActiveCell.Offset(14, 6).Range("A1").Select
    ActiveWindow.SmallScroll ToRight:=6
    ActiveWindow.SmallScroll Down:=8
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWindow.FreezePanes = True
    Range("IV1").Value = 1
    Else
    If Range("IV1").Value = 1 Then
    ActiveWindow.FreezePanes = False
    ActiveWindow.SmallScroll ToRight:=-6
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    Range("IV1").Value = 0
    End If
    End If
    End Sub

    Derek

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface

    Getting a EBUG error on u r 1st line of code.
    Any suggestions?

    [ This Message was edited by: Zac on 2002-02-28 06:24 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for your private message, Zac.

    [ This Message was edited by: Mudface on 2002-02-28 07:42 ]

    [ This Message was edited by: Mudface on 2002-02-28 08:13 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com