How to hide pages upon start up

[aub1202]

Hello, I read a couple of forums about "forcing" my clients to enable macros before continuing to view the entire work book.... so I ended up copying and pasting a code into my work book which works great. Basically, when the work book is opened one worksheet appears on this sheet I have a text box warning about enabling macros in order to view the rest of the worksheet. Once the customer enables the macros the warning sheet disappears and all the other sheets in my workbook open and are visible. This all works great, my problem is that I do not want ALL of my worksheets to be open, just one! how do I continue to hide the other worksheets? I've tried messing with the code but because I am inexperienced I don't want to mess up something with my opening macro enabling sheet. Can someone please take a look at it, it seems like it should be a simple fix I just can't figure it out! Here is my current coding, I feel like the problem occurs in the ending sub that says "Showallsheets"

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden


End Sub

 

[dmt32]

Hi welcome to the forum.
You just need to modify the ShowAllSheets code with simple If statement. Enter the name of the worksheet (between the quotes "") you want to remain visible I have shown in red.

Private Sub ShowAllSheets()
'Show listed worksheets only and
'hide the macro welcome page
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Sheet1" Then
            ws.Visible = xlSheetVisible
        Else
            If ws.Name <> WelcomePage Then ws.Visible = xlSheetVeryHidden
        End If
    Next ws
   'hide macro sheet
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

Hope helpful

Dave