Macro Back Button

[ianripping]

Is it possible to write a Macro which goes back to the previous page
viewed?

Say I went from sheet one to sheet two.
Can I make a Macro that would remember that I had come from sheet one
and then send me back to sheet one when I pressed the button of which I
have assigned the Macro to?

 

[Bob Phillips]

Ian,

It's fairly simple.

Put this code in a normal code module

Public iCurrSheet As Long
Public iPrevSheet As Long

Sub GoBack()
If iCurrSheet <> iPrevSheet Then
Worksheets(iPrevSheet).Activate
End If
End Sub


and this in ThisWorkbook code module


Private Sub Workbook_Open()
iCurrSheet = 1
Worksheets(1).Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
iPrevSheet = iCurrSheet
iCurrSheet = ActiveSheet.Index
End Sub

and link your button to the GoBack procedure.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ianripping]

How do I enter code into the Workbook Code?

 

[ianripping]

Its ok I've figured it out.

This is great.
Thankyou!

 

[Dennis]

Maybe to those in the know - my question may be simplistic.

What is the difference between:

1) Normal Code module (where specifically in the VBA Edit Window in either or both the Personal.xls or current workbook)
2) ThisWorkbook module (seems obvious; but again where in the VBA Edit Window i.e. as part of a routine or stand alone)

Dennis

 

[Bob Phillips]

Dennis,

A normal code module is the type of module that you get when you do
Module>Insert

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Maybe to those in the know - my question may be simplistic.

What is the difference between:

1) Normal Code module (where specifically in the VBA Edit Window in either

or both the Personal.xls or current workbook)

2) ThisWorkbook module (seems obvious; but again where in the VBA Edit

Window i.e. as part of a routine or stand alone)

 

[Dana DeLouis]

This is not the best solution, but you may find it interesting as it does
not use any variables.

'// ThisWorkbook Module
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Goto Selection
End Sub


'// Module Sheet
Sub GoBack()
With Application
.EnableEvents = False
Sheets(.PreviousSelections(2).Parent.Name).Activate
.EnableEvents = True
End With
End Sub