VB code for a "return to previous sheet" button

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
I have a workbook with several sheets. On each one I have a button that takes you to a specific sheet (this is kind of like a free-format notes page for the user to do with what they will). On this "notes" sheet, I'd like a button that, when clicked, takes you back to the sheet you were previously on. So if you were on sheet 1 when you clicked the button to go to the notes page, you'd be sent back to sheet 1. If you were on sheet 2, you'd be sent back to sheet 2 and so on.

Is this possible?

Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Andrew

Declare a public variable in the declarations section of a standard module in your workbook:

Code:
Public shName as String

Then, at the start of your code that is assigned to the macro buttons on each sheet (that takes you to the specific notes sheet) include the following:

Code:
shName = ActiveSheet.Name

Then, for the macro on the notes sheet that takes you back to the previous sheet use:

Code:
Sheets(shName).Activate

Hope this helps!

Richard
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
To Standard module
Code:
Public wsName(1)

Sub test()
Sheets(wsName(0)).Activate
End Sub
To ThisWorkbook module
Code:
Private Sub Workbook_Open()
wsName(0) = ActiveSheet.Name
wsName(1) = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
wsName(0) = wsName(1)
wsName(1) = Sh.Name
End Sub
To Sheet module
Code:
Priavate Sub CommandButton1_Click()
test
End Sub
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
That works a treat. Thank you very much.

And thanks to Jindon for the effort you put into that.

I owe you all.
 

Forum statistics

Threads
1,141,757
Messages
5,708,340
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top