Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalling a previously active worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalling a previously active worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalling a previously active worksheet
Maybe Jan Karel Pieterse's GoBack utility will help:
http://www.jkp-ads.com/Download.asp Look for goback.zip jonco wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to hide a portion AS WELL AS it's formulas, then recalling | Excel Worksheet Functions | |||
Recalling an array of data | Excel Discussion (Misc queries) | |||
Help with sebastienm's code - Returning To Previously Selected Worksheet | Excel Programming | |||
I need help with recalling information | Excel Discussion (Misc queries) | |||
Selecting Previously Active Cell | Excel Programming |