Back button

[Daxton A.]

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?

[Scottie P]

Hi Daxton.

I am sure (without a doubt) that I shamelessly :roll: 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

[mark007]

You can shorten:


Worksheets(ThisWorkbook.PrevWb.Name).Activate

To


PrevWb.Activate

:)

[Scottie P]

Thanks for changing that Mark...I wouldn't have thought about it.

:)

[Daxton A.]

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

[mark007]

Which line is this on?

[Daxton A.]

Sub PrevWb()
Worksheets (ThisWorkbook.PrevWb.Activate)
End Sub

[mark007]

Use:


PrevWb.Activate

:)

[Zack Barresse]

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

[Scottie P]

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.

[Zack Barresse]

I get the same error Scott, I just left it the same with the one addition. Works like a charm for me.

[Daxton A.]

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.

[Zack Barresse]

Did you save the workbook w/ the code, then reopen it? It does have a workbook_open procedure called.

[Daxton A.]

But I just tried that and when I re-opened it, it gave me the error.

[Zack Barresse]

Zip the file and post it. :)

[Daxton A.]

I'm sorry it took me awhile to respond, I was at lunch.

[mvidas]

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

[Daxton A.]

It works now.

Thank All of you.

This one is solved.

[Scottie P]

Hi Daxton.

If you are good to go on this question I will mark it solved.

Scott