Here's a simple MRU implementation. Not fully tested, but should give you an
idea.
Find others ways using a collection etc he
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
"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