Button to point to worksheet and back to source

[ricco]

Hello Forum - first post. Thanks for any help or guidance. I have a several worksheet workbook that walks a user through a flow-process to get to an end result. On each worksheet I have a button to point to a single hidden worksheet that I have entered supporting information, such as directions and references.

I can place the button on each worksheet to get to the reference worksheet, but I'd like to add a "BACK" button on the reference worksheet that would send me back to the original worksheet the user was on when they hit the reference button. So how do I capture that?

For example, the reference button is on each worksheet - there are 5 worksheets. If I am on the second worksheet and I hit the reference button, it goes to the reference worksheet. On the reference worksheet, when I hit "back" I want to go back to the second worksheet. If I am on the third worksheet and I hit the reference button, I'd like to go back to the third worksheet, etc etc.

 

[AgentSmith]

Try storing the original sheet in a Name object like this:

Names("PreviousSheet").RefersTo = ActiveSheet.Name

Then all your back button needs to do is pick this up. Note that it will be stored as a formula rather than a value but you can use Evaluate or square brackets to work around that, like this:

Dim GotoSheet As String
GotoSheet = Evaluate(Names("PreviousSheet").RefersTo)
'or ...
GotoSheet = [=PreviousSheet]

You'll need to create the name first - just name a dummy cell for example. However once it's there you can just update it using the above.

 

[HalfAce]

Another idea is to use a public variable for the 'previous' sheet's name. (ie.)

Public BackToSheet As Worksheet
'''/// The code for buttons on all sheets but "Reference"
Sub GoToReferenceSheet()
Set BackToSheet = ActiveSheet
Sheets("Reference").Select
End Sub

'''/// The code for your 'Back' button on Reference sheet.
Sub GoBack()
BackToSheet.Activate
Set BackToSheet = Nothing
End Sub

Depending on which type of buttons you're using for this, you could make it a bit more fun by having it display the previous sheet's name on the back button. (Back to Sheet5 - or - Back to Finance Sheet) or something like that.

Hope it helps.

 

[HalfAce]

Just for fun, here's what I was talking about above.

Start by installing an ActiveX button (command button from the Controls Toolbox toolbar) on a sheet named "Reference".
Then, in the button's name properties, name it BackButton
Then assign this code to the button (in the sheet's code module)

Private Sub BackButton_Click()
Sheets(Right(BackButton.Caption, Len(BackButton.Caption) - 8)).Activate
BackButton.Caption = ""
End Sub

Next, install the same kind of button (ActiveX CommandButton) on every sheet you'll want to get to the Reference sheet from.
You'll want to go into the name property (of EACH button individually) and give them all the same name: GoToReference
Next, in the sheet code module of each sheet, paste in this code.

Private Sub GoToReference_Click()
Dim BackToSheet As String
BackToSheet = ActiveSheet.Name
With Sheets("Reference")
    .BackButton.Caption = "Back to " & BackToSheet
    .Activate
End With
End Sub

Now just make sure your Design Mode is turned back off, (make sure the button on Reference sheet is big enough to display all sheet names) you should be ready to check it out.

Hope it helps.