Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
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 "ketut" wrote in message ... 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
On 2 July, 12:17, "Patrick Molloy" wrote:
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 "ketut" wrote in message ... 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). 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
On 2 July, 12:19, Mike H wrote:
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). 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
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" wrote: On 2 July, 12:19, Mike H wrote: 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). 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel- Go to Previous Sheet
On 2 July, 13:30, Mike H wrote:
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" wrote: On 2 July, 12:19, Mike H wrote: 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). 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import data from previous sheet in excel | Excel Discussion (Misc queries) | |||
Word link to open an excel book in a previous determined sheet | Links and Linking in Excel | |||
transfer contents from a cell in previous sheet if the sheet is a | New Users to Excel | |||
Excel Charting Macro, that deletes previous sheet | Excel Discussion (Misc queries) | |||
Select sheet, Pause till Enter pressed, return to previous sheet | Excel Programming |