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.

 

[ver101]

Hi, How can I copy this code to my personal.xls so i may apply it to all my excel files?



QUOTE=Mike H;13354788]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" wrote:

> 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).
>[/QUOTE]

 

[skillet]

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

Thank you that is very helpful and exactly what I was looking for for several weeks now. A few other questions, is there a way to go back to what you were on once you run the macro? So a toggle between sheets that are not necessarily adjacent to each other. Running the macro only works once.

Second question is I am on a Excel on a Mac and tried storing these in the Personal Macro Workbook but it gave me an error. I would ideally like this to work in every Excel workbook I have open but maybe that is not possible.

 

[skillet]

Bump

 

[skillet]

Bump

This might not be an active forum or I am asking the wrong question that no one knows the answer to.