Using the Same Button to reverse macro

[Zac]

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

 

[Derek]

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

 

[Mudface]

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.

 

[Zac]

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.

 

[Mudface]

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

 

[Derek]

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

 

[Zac]

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

 

[Mudface]

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