PDA

View Full Version : Solved: Back button



Daxton A.
07-20-2004, 09:45 AM
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
07-20-2004, 04:07 PM
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:


' In a Standard Module
Option Explicit

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


This was tested and works in Office XP.

Scott

mark007
07-21-2004, 06:57 AM
You can shorten:

Worksheets(ThisWorkbook.PrevWb.Name).Activate

To

PrevWb.Activate

:)

Scottie P
07-21-2004, 08:27 AM
Thanks for changing that Mark...I wouldn't have thought about it.

:)

Daxton A.
07-22-2004, 06:38 AM
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
07-22-2004, 07:01 AM
Which line is this on?

Daxton A.
07-22-2004, 07:19 AM
Sub PrevWb()
Worksheets (ThisWorkbook.PrevWb.Activate)
End Sub

mark007
07-22-2004, 08:07 AM
Use:

PrevWb.Activate

:)

Zack Barresse
07-22-2004, 08:48 AM
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
07-22-2004, 08:54 AM
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
07-22-2004, 08:58 AM
I get the same error Scott, I just left it the same with the one addition. Works like a charm for me.

Daxton A.
07-22-2004, 09:10 AM
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
07-22-2004, 09:18 AM
Did you save the workbook w/ the code, then reopen it? It does have a workbook_open procedure called.

Daxton A.
07-22-2004, 09:20 AM
But I just tried that and when I re-opened it, it gave me the error.

Zack Barresse
07-22-2004, 09:28 AM
Zip the file and post it. :)

Daxton A.
07-22-2004, 11:22 AM
I'm sorry it took me awhile to respond, I was at lunch.

mvidas
07-22-2004, 11:36 AM
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.
07-22-2004, 11:56 AM
It works now.

Thank All of you.

This one is solved.

Scottie P
07-25-2004, 03:09 PM
Hi Daxton.

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

Scott