Excel- Go to Previous Sheet

[ketut]

Hi all,

Please help. I am in need to find the VBA code to enable me to set a
Go to previous sheet button on one of the sheets in my report.
The report has 10 sheets. 1 of these sheets can be accessed from any
of the other 9, but I would like whoever access it can go back to
where they were but a click of a button. Please help me and tell me
how can do that?
I can copy and past the code to excel provide you tell me what it
is....

Thank you so much in advance guys.
( I know nothing about VBA or codings).

 

[Patrick Molloy]

just use a hyperlink ... set it to go to your main sheet. no VBA required

Insert /Hyperlink

choose "place in this document" and you'll get a default addrress like
Sheet1!A1
amend this appropriately. copy paste to all the other sheets

 

[Mike H]

Hi,

This requires 2 bits of code.
Alt+F11 to open VB editor and double click 'Thisworkbook' and paste this
code in on the right

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

When ever a user changes sheets the name of the last sheet is now held in
the variable 'Lastsheet' so if you assign a shortcut or button to the code
then the last used sheet will be selected.

Mike

 

[ketut]

just use a hyperlink ... set it to go to your main sheet. no VBA required

Insert /Hyperlink

choose "place in this document" and you'll get a default addrress like
Sheet1!A1
amend this appropriately. copy paste to all the other sheets

Thank you Patrick for your assistance. I have tried that option but it
can only direct me to 1 destination. but I was rather looking for
something that work like a "BACK" browser botton. which brings you
back to the previous page irrespective of where you are within the
excel report.

 

[ketut]

Hi,

This requires 2 bits of code.
Alt+F11 to open VB editor and double click 'Thisworkbook' and paste this
code in on the right

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

When ever a user changes sheets the name of the last sheet is now held in
the variable 'Lastsheet' so if you assign a shortcut or button to the code
then the last used sheet will be selected.

Mike

Hi Mike,
Thank you so much for your prompt response. that was very quick.
I have done what you said, however I have just 1 more question:
what should I replace "Sh.name" with because I keep getting a
"subscript out of range error". when I click on Debug the highlighted
area is "Sheets(lastsheet).Select"
many thanks in advance.
Kal

 

[Mike H]

Hi,

You must put the code exactly where I indicated

Alt+F11 to open VB editor and double click 'Thisworkbook' and paste this
code in on the right

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

The important bit is the Public declaration of lastsheet but it doesn't
become populated until you actually change sheet.

Mike

 

[ketut]

Hi,

You must put the code exactly where I indicated

Alt+F11 to open VB editor and double click 'Thisworkbook' and paste this
code in on the right

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

The important bit is the Public declaration of lastsheet but it doesn't
become populated until you actually change sheet.

Mike

Hi Mike,

I shall thank you again for your efforts to help me and others.
Would it be too much to ask if you can email me a sample on a 3 tabs
excel document. I have just spent most of the night trying to do and
re do what you advised me to, but I am getting it wrong.
Many thanks.