Selecting last active sheet before macro?
Posted by Danny on October 10, 2000 12:07 PM
I have several macros assigned to a worksheet menubar I created (thanks to Ivan). All of these macros select certain sheets and perform routines. I would like to be able to return to the previously active sheet when the button was pressed. I would think I would have to do something like set a variable to the active sheet at the beginning of each macro then recall it at the end, but can't figure that out. Can someone help me please. Thanks.
Posted by Ben O. on October 10, 2000 12:45 PM
This should work. I just tested it:
Sub Macro1()
Dim x As String
x = ActiveSheet.Name
.
.
.
Sheets(x).Select
End Sub
You also might want to consider performing routines on worksheets without selecting them, like this:
With ActiveWorkbook.Sheets("Sheet Name")
.PrintOut Copies:=1, Collate:=True
End With
Just a suggestion.
-Ben
Posted by Ivan Moala on October 11, 2000 4:22 AM
You could also try something like this;
So that in your command button routine
if you wish to save a location just call
SetSaveLoc
To return to this location then call
GetSaveLoc
Public Sub SaveLocation(ReturnToLoc As Boolean)
Static WB As Workbook
Static WS As Worksheet
Static R As Range
On Error GoTo NoGo
If ReturnToLoc = False Then
Set WB = ActiveWorkbook
Set WS = ActiveSheet
Set R = Selection
Else
WB.Activate
WS.Activate
R.Select
End If
Exit Sub
NoGo:
MsgBox "Not set !"
End Sub
'To save the current location, call SetSaveLoc.
Public Sub SetSaveLoc()
SaveLocation (False)
End Sub
'To return to the saved location, call GetSaveLoc.
Public Sub GetSaveLoc()
SaveLocation (True)
End Sub
