Worksheets

R

Rope

Hello,
Is it possible to create a macro or VBA to return to the previous sheet
(the one I just left, not the one next to it)? I leave the sheet to open a
different one and then want to quickly return to the first.Does that make
sense?
 
R

Rick Rothstein

Explain the part about "leaving the sheet to open a different one"...
exactly what are you doing there and show the code you are using to do it?
 
J

Joshua Fandango

Hi Rope,

Bit of an ambiguous question; do you want to prevent a user selecting
a different sheet?
The following makes Sheet1 the active sheet whenever another sheet is
selected when you put the code in the Sheet1(Sheet1) module:

Option Explicit
Private Sub Worksheet_Deactivate()
Sheet1.Activate
End Sub

HtH,
JF.
 
R

Rope

Hi,
Thanks for the response. I knew that explanation was vague. What I am
trying to do is as follows: I am working on an active worksheet entering
data, I use a macro to jump to a another specific worksheet that contains
embedded forms that cover all the worksheets (memory limitations). After I
complete and print the form, I would like to bounce back to the same
worksheet I was entering the data into originally to complete anything else
that needs to be done. It would be nice to use a code/macro that brings me
back to the worksheet without having to search for it by using the horizontal
scroll bar (I have "alot" of sheets in this workbook). I hope that explains
my situation. I appreciate your assistance.
Rope
 
P

Per Jessen

Hi

Maybe something like this:

Sub AAA()
OrgSheet=activesheet.name

'Your code

Sheets(OrgSheet).activage

End Sub

Regards,
Per
 
C

Chip Pearson

In a regular code module name Module1, use

Public PrevSheet As Worksheet

Sub GoBack()
PrevSheet.Select
End Sub

Then in the ThisWorkbook module, use

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set Module1.PrevSheet = Sh
End Sub

This allows only one level of going back. When you run GoBack (you can
assign it to a keyboard shortcut if you want), you will go to the
previously selected sheet.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Top