Code for Returning to Last Active Worksheet Please
This is what I actually implemented:
Put this in Microsoft Excel Objects ThisWorkbook
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
PrvusActvSht = Sh.Name
End Sub
'Put this at the top of a module window:
Global PrvusActvSht As String
'Put this in a module:
Sub SetActiveCellLocOnAllSheets()
On Error GoTo Error1
MyMsg = vbCr & " This will set the active cell location for all Sheets
to the same cell." & _
vbCr & vbCr & vbCr & "Enter the cell you wish to be active (ie: U4)"
MyTitle = "Set Active Cell Location"
Application.ScreenUpdating = False
HomeSht = PrvusActvSht
Worksheets(PrvusActvSht).Activate
PrevCell = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
HomeCell = InputBox(MyMsg, MyTitle, PrevCell)
If HomeCell = "" Then GoTo Error1
For WkSht = 1 To 24
Worksheets(WkSht).Select
Range(HomeCell).Select
ActiveWindow.ScrollRow = ActiveCell.Row
Next WkSht
Exit1:
Application.ScreenUpdating = True
Worksheets(HomeSht).Select
Exit Sub
Error1:
Worksheets(HomeSht).Select
Application.ScreenUpdating = True
End Sub
To use, put a button on one of the worksheets and set the code to call
SetActiveCellLocOnAllSheets
|