Recalling a previously active worksheet
"jonco" wrote in message
om...
I have a workbook with about 90 worksheets each with a different number as
the sheet name.
They are named when created using a customer number. I work with a
sheet
(the Active Sheet) and then when done have a macro go to an index sheet
which lists all the sheets.
Sometimes I need to make a change to the last transaction. What I want to
do is to have a macro that would go to the last active worksheet (named
as
a customer number).. for instance 1847). I can write the macro to make
the
changes I want but don't know how to refer to the last sheet that was
active. I can't use it's number because it's constantly changing.
How can I store and recall the last shet that was used before I went back
to
the index sheet?
Thanks in advance for the assistance.
Jonco
Put following code into the ThisWorkbook codepage:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
DeactivatedSheet = Sh.Name
End Sub
Now you can reference the last sheet. E.g. put a "Back" button on a userform
and attach following code:
Public DeactivatedSheet
Sub CommandButton1_Click()
On Error Resume Next
Worksheets(DeactivatedSheet).Activate
End Sub
Above code declares the variable DeactivatedSheet as public so that it is
recognized in all modules.
Cheers,
Joerg
|