Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning To Previous Worksheet - Help with sebastienm's code
Hi all, I'm trying to utilise the following code detailed by sebastienm in this post, http://www.excelforum.com/showthread...rning+previous However once complete and I have tried to test the Macro the following error is returned - "Run time error 91 - Object variable or with block variable not set" In debug the following piece of code from the second module is highlighted - "Tracker.GotoLastSheet" Can anyone help me in working out where I have gone wrong? I am a relative novice in this area so apologies if it is a obvious mistake on my half. Many Thanks in advance, AcesUp ' ------------------------------------------------------ Option Explicit Private WithEvents App As Application Private mLastSheet As Object '------------------------------------ Public Property Get LastSheet() As Object Dim s As String If mLastSheet Is Nothing Then Set LastSheet = Nothing Else On Error Resume Next 'capture automation error if book has been closed s = mLastSheet.Name If Err < 0 Then Set mLastSheet = Nothing Set LastSheet = Nothing Else Set LastSheet = mLastSheet End If End If End Property Public Sub GotoLastSheet() If Not LastSheet Is Nothing Then On Error Resume Next 'if hidden book or other error LastSheet.Parent.Activate LastSheet.Activate End If End Sub '------------------------------------ Private Sub App_SheetDeactivate(ByVal Sh As Object) Set mLastSheet = Sh End Sub Private Sub Class_Initialize() Set App = Application End Sub Private Sub Class_Terminate() Set App = Nothing End Sub '----------------------------------------------------- -As you can see in the above code, when a sheet is Deactivated the mLastSheet variable is set, keeping track of that deactivated sheet. Also there is 2 public methods, one that returns the Last Sheet, the other one is a sub (GotoLastSheet) that activates the last sheet. - Now in a regular module, create a public variable of the above class and add a Sub that calls the GotoLastSheet of the above class.- '---------------------------------------------------- Option Explicit Public Tracker As ClsSheetTracker Sub GotoLast() Tracker.GotoLastSheet End Sub '---------------------------------------------------- -- AcesUp ------------------------------------------------------------------------ AcesUp's Profile: http://www.excelforum.com/member.php...o&userid=26519 View this thread: http://www.excelforum.com/showthread...hreadid=548093 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Needed: worksheet export help | Excel Discussion (Misc queries) | |||
Help, need to speed up this macro | Excel Discussion (Misc queries) | |||
How do I pull data from a previous worksheet? | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Referring to Previous Worksheet - coding | Excel Worksheet Functions |