VB code for a "return to previous sheet" button

[AndrewRossington]

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

 

[Richard Schollar]

Hi Andrew

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

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:

shName = ActiveSheet.Name

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

Sheets(shName).Activate

Hope this helps!

Richard

 

[jindon]

To Standard module

Public wsName(1)

Sub test()
Sheets(wsName(0)).Activate
End Sub

To ThisWorkbook module

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

Priavate Sub CommandButton1_Click()
test
End Sub

 

[AndrewRossington]

That works a treat. Thank you very much.

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

I owe you all.