Command button to go back to a sheet

[engmgriff]

Hi

I am trying to create a cmd button which will enable me to go back t
the previous worksheet I was in. However I have a number of worksheet
to get back in from this one button. See below for example.

Say I have Worksheet1, Worksheet2 and Worksheet3, each having at leas
10 buttons on, which once pressed will take me into worksheet4. Th
button in worksheet4 will say back to previous screen, this depend
which button was pressed and from which worksheet1,2 or 3 I want to g
back to.

Thank you in advance for your help.

Mat

 

[Rob van Gelder]

You need to maintain a "PreviousWorksheet" variable and update it when a
button is clicked.

 

[engmgriff]

Thanks, but I am new to programming and could do with some more help. I
normally use macros, but dont think I can with this problem. Anymore
help would great.

Thanks

Matt

 

[Bob Phillips]

Matt,

Here is one way

Put this code in the ThisWorkbok code module

Public PrevWb As Worksheet

Private Sub Workbook_Open()
Set PrevWb = ActiveSheet
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set PrevWb = Sh
End Sub


and then add this code to a standard code module

Sub PrevWb()
Worksheets(ThisWorkbook.PrevWb.Name).Activate
End Sub

and link the PrevWb macro to your button.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Wrong Matt. See my reply.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CST]

Hello,

Please try the following (I assume 2 worksheets, but can be as many as
you want):

Dim strPrevSheet As String

Sub cmdNext()
strPrevSheet = ActiveSheet.Name
Sheets("Sheet2").Activate
End Sub

Sub cmdBack()
Sheets(strPrevSheet).Select
End Sub

Create a new module can a couple of sub routines. Declare a global
variable called strWhatever. I assume you already have the next
button figured out, so am hard coding that here. The previous button
is simply taking the current sheets name you are on and then assigning
that to the global strWhatever. This in a sense lets you maintain
state. Again, this is a simple example and has much to be improved
upon, but should get you going.

HTH

 

[Wouter]

Hi Matt,

My idea:

In VBE add is necessary create a module to add this line:

Public strPrev As String

For WorkSheet1, WorkSheet2 and WorkSheet3 add this event handler

Private Sub Worksheet_Deactivate()
strPrev = Me.Name
End Sub

For WorkSheet4 add a commandbutton, name it cmdPrev and give it this event handler:

Private Sub cmdPrev_Click()
If strPrev < "" Then
Sheets(strPrev).Activate
End If
End Sub


Good Luck
Wouter HM