You see what I want is a back button in case i forgot something on the prev sheet i was just on. Does anyone know how to do this?
You see what I want is a back button in case i forgot something on the prev sheet i was just on. Does anyone know how to do this?
Hi Daxton.
I am sure (without a doubt) that I shamelessly lifted this from someone, somewhere...hope it gets you what you need:
' This code goes into the ThisWorkbook Module
Option Explicit Public PrevWb As Worksheet Private Sub Workbook_Open() Set PrevWb = ActiveSheet End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Set PrevWb = Sh End Sub
And then this code goes into a (Standard) Module:
Option Explicit Sub PrevWb() Worksheets(ThisWorkbook.PrevWb.Name).Activate End Sub
This was tested and works in Office XP.
Scott
Life is Visual: Presence is Perception...
How we see the world is how we respond to it. ~* Peace *~
You can shorten:
ToWorksheets(ThisWorkbook.PrevWb.Name).Activate
PrevWb.Activate
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA | The Code Net
Thanks for changing that Mark...I wouldn't have thought about it.
Life is Visual: Presence is Perception...
How we see the world is how we respond to it. ~* Peace *~
It came up with a compile error in the module.
It said:
Compile Error:
Method or Data Member Not Found
And it had .PrevWB highlighted.
When this does work, will it go back to previous workbook or worksheet?
Because what I'm trying to do is go back to the previous worksheet. And I'm making this at work, so I was off yesterday is why it took me a while to respond.
Thank You for all your Help Scott & Mark007
Which line is this on?
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA | The Code Net
Sub PrevWb() Worksheets (ThisWorkbook.PrevWb.Activate) End Sub
Use:
PrevWb.Activate
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA | The Code Net
Hi,
I tweaked the standard module code as so it won't error out if you haven't left the current sheet. Seems to work ok...
Option Explicit Sub PrevWb() On Error GoTo nada Worksheets(ThisWorkbook.PrevWb.Name).Activate Exit Sub nada: MsgBox "You haven't left the current sheet yet." End Sub
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Daxton and Mark,
If I use the code as I first posted, SAVE the file, move to a new sheet and then run the macro I do go back to the sheet I was just looking at.
If I use the code with the change to 'PrevWb.Activate' I get runtimes.
Life is Visual: Presence is Perception...
How we see the world is how we respond to it. ~* Peace *~
I get the same error Scott, I just left it the same with the one addition. Works like a charm for me.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
It still gives me an error saying that .PrevWB can't be found. It says this exactly: Compile Error: Method or Data Member Not Found
Is this b/c I'm using Office 2000 or is .PrevWB supposed to be standing for the workbook name like Worksheets(ThisWorkbook.Phonebook.Name)Activate
And when I try to run another Module it says:
Compile Error:
Expected Function or Variable
on the Workbook Module.
Did you save the workbook w/ the code, then reopen it? It does have a workbook_open procedure called.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
But I just tried that and when I re-opened it, it gave me the error.
Zip the file and post it.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
I'm sorry it took me awhile to respond, I was at lunch.
Hi Daxton,
In your ThisWorkbook, find the part that says
Option Explicit Public PrevWb As Worksheet
which you have between sheetactivate and sheetdeactivate, and move it to the very top of the module. Public variables have to be declared at the top of the module.
Matt
It works now.
Thank All of you.
This one is solved.
Hi Daxton.
If you are good to go on this question I will mark it solved.
Scott
Life is Visual: Presence is Perception...
How we see the world is how we respond to it. ~* Peace *~