Need help recalling previous sheet name

[rbi]

I new to VBA and need some direction. I have a workbook with multiple sheets used to configure various products. On each page there's a command button to make a recap page visible, hiding the sheet that called it. What I can not figure out is, when I'm finished with the recap page, how do I then hide it and make the page that called it visible again. Here's my code. It fails to with subscrip out of range on the line marked with . Any help is appreciated as I'm a bit in over my head.

' This is in sheet1 code
Sub ViewSPR_Click()
ViewSPR.TakeFocus******* = False
Run ("PassSheetName") 'Capture activesheet.name & assign to CurrentSheet
Run ("Hidesheets") 'Hides all sheets except Main
Sheets("SPR-Submit").Visible = True
Sheets("Main").Visible = xlVeryHidden
End Sub
________________________________

' Then From Module1 .....
Public Sub PassSheetName()
Dim CurrentSheet As String
CurrentSheet = ActiveSheet.Name
End Sub
_________________________________
'And finally from Sheet7 here's the fail
Private Sub CommandButton1_Click()
Run ("HideSheets")
Sheets(CurrentSheet).Visible = True
Sheets("Main").Visible = xlVeryHidden
End Sub
_________________________________

 

[Norie]

If you wanted to do this with a string variable for the sheet name you would need to declare CurrentSheet as a public variable.

At the moment it's declared in the PassSheet sub and goes out of scope as soon as that finishes executing.

I'm not quite sure how you can do what you want because that isn't totally clear, to me anyway.

 

[rbi]

Thanks Norie. I know I'm not communicating well as I don't understand the concepts, the taxonomy or nomenclature. (One could say I'm at a bit of disadvantage) So let me take another stab at the desired outcome. Once I reach the "SPR-Submit" page from any page, I want to click a command button and go back to the page that I was on immediatly prior to the "SPR-Submit" page. Does that make sense?

 

[AlphaFrog]

Try something like this...

In Module1

Public PrevWorksheet As Worksheet

Sub Hidesheets()
    
    Dim ws As Worksheet
    
    Sheets("Main").Visible = xlSheetVisible
    
    For Each ws In Worksheets
        If ws.Name <> "Main" Then ws.Visible = xlVeryHidden
    Next ws
    
    
End Sub

PrevWorksheet is a Persistent and Global variable available to other procedures in all modules.


This is in the Worksheet "Main" module

Private Sub CommandButton1_Click()
    PrevWorksheet.Visible = xlSheetVisible
    Worksheets("Main").Visible = xlVeryHidden
End Sub

This is in each of the other worksheet modules.

Private Sub ViewSPR_Click()
    Set PrevWorksheet = ActiveSheet
    Module1.Hidesheets
End Sub

You will need a way (code) to select other sheets that are hidden.

 

[mikerickson]

Altenatly, you could call the recap page my making a new window. There is no need to remember the previous page. (Rather it is stored in the old window)

Dim recapWindow As Window

ActiveWindow.NewWindow
ThisWorkbook.Sheets("Recap").Activate
Set recapWindow = ActiveWindow

Rem more code

recapWindow.Close

 

[mikerickson]

If you do need to refer to a workbook's previously active sheet, you can add a .PreviouslyActiveWorksheet property to the workbook, by putting this in the ThisWorkbook module

Dim prevActSheet As Worksheet

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

Public Property Get PreviouslyActiveWorksheet() As Worksheet
    Set PreviouslyActiveWorksheet = prevActSheet
End Property

It would be used like

MsgBox ThisWorkbook.PreviouslyActiveWorksheet.Name

On opening a workbook, ThisWorkbook.PreviouslyActiveWorksheet is Nothing until another sheet is activated.

 

[rbi]

Sub ThanksAMillion))
Dim Appreciation as Genuine

Thanks everyone, this bird has taken flight ! Norie, I used your suggestion first of declaring the CurrentSheet as a public variable. Then alphafrog your "for - next" construct slimmed down another piece of the pie and it actually executes a bit snappier. WOW I'm impressed and so thankfull for all the great help I have recieved here.

End Sub