Recalling a previously active worksheet

[jonco]

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

 

[Joerg]

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

 

[NickHK]

Here's a simple MRU implementation. Not fully tested, but should give you an
idea.
Find others ways using a collection etc here:
http://support.microsoft.com/kb/q253171/
http://vb-helper.com/howto_mru_list.html

'**** <cWS_MRU in a Class module>
Option Explicit
Option Base 1

Const DFT_MRU_COUNT As Long = 10
Private mWSNames() As String
Private mMRUCount As Long
Private mNextEntry As Long

Public Property Let MRUCount(vData As Long)
mMRUCount = vData
If vData < UBound(mWSNames) Then
If MsgBox("The MRU is being scaled down. Some entries may be lost. OK
?", vbExclamation + vbOKCancel) = vbCancel Then
Exit Property
End If
End If
ReDim Preserve mWSNames(vData)
If mNextEntry > vData Then mNextEntry = vData
End Property

Public Property Get MRUCount() As Long
MRUCount = mMRUCount
End Property

Public Function MRUAdd(WSName As String)
mWSNames(mNextEntry) = WSName
If mNextEntry = mMRUCount Then
mNextEntry = 1
Else
mNextEntry = mNextEntry + 1
End If
End Function

Public Function MRUClearAll()
Erase mWSNames
ReDim mWSNames(mMRUCount)
mNextEntry = 1
End Function

Public Function MRUGetPrevious(Optional PreviousCount As Long = 1) As String
Dim Element As Long
Element = mNextEntry - (PreviousCount Mod mMRUCount)
If Element < 1 Then Element = mMRUCount - Element
MRUGetPrevious = mWSNames(Element)
End Function

Private Sub Class_Initialize()
'Set initial values
ReDim mWSNames(1 To DFT_MRU_COUNT)
mMRUCount = DFT_MRU_COUNT
mNextEntry = 1
End Sub
'**** </cWS_MRU in a Class module>

'**** <Standard module>
Public MRU As cWS_MRU
'**** </Standard module>

'**** <ThisWorkbook>
Private Sub Workbook_Open()
Set MRU = New cWS_MRU
MRU.MRUCount = 12
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If MRU.MRUGetPrevious(1) <> "" Then MsgBox "Previous sheet was " &
MRU.MRUGetPrevious(1)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MRU.MRUAdd Sh.Name
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Dim TempStr As String
TempStr = MRU.MRUGetPrevious(2)
If TempStr <> "" Then Worksheets(TempStr).Activate
End Sub
'**** <ThisWorkbook>

NickHK

 

[Dave Peterson]

Maybe Jan Karel Pieterse's GoBack utility will help:
http://www.jkp-ads.com/Download.asp

Look for goback.zip