Force Excel Macro With Some Sheets Remain Hidden

[yeo_1987]

Hi pals,

I'm a newbie who just get started to explore the VBA field and need your help on the writing of VBA.

I use the code written by Ken Puls as shown below to force enable macro but need some modifications to suit my needs:

1. Instead of showing all sheets after enabling the macro, I want to make some sheets (e.g. sheets named "Data", "Reference") remain hidden.

2. Put a password to the macro to prevent users from editing it

Code written by Ken Puls is as follow:

---------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------------

Hope you guys can assist me. Thanks in advance

[Leith Ross]

Hello yeo_1987,

Change the macro "ShowAllSheets" to this...

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


Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case Is = WelcomePage, "Data", "Reference": ws.Visible = xlSheetVeryHidden
            Case Else: ws.Visible = xlSheetVisible
        End Select
    Next ws
    
End Sub

To protect the VBA Code...

1. Press Alt+F11 to Open the VBA Editor
2. Press Alt+t to Open the VBA Project Properties
3. Press e and Click on the Protection tab

NOTE: Save your password. If you forget it, you will not be able to recover it and you will not be able to make changes to the VBA code.

[yeo_1987]

Hello yeo_1987,

Change the macro "ShowAllSheets" to this...

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


Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case Is = WelcomePage, "Data", "Reference": ws.Visible = xlSheetVeryHidden
            Case Else: ws.Visible = xlSheetVisible
        End Select
    Next ws
    
End Sub

To protect the VBA Code...

1. Press Alt+F11 to Open the VBA Editor
2. Press Alt+t to Open the VBA Project Properties
3. Press e and Click on the Protection tab

NOTE: Save your password. If you forget it, you will not be able to recover it and you will not be able to make changes to the VBA code.

Dear Leith Ross,

Thank you very much for your help. I inserted the code but somehow ended with run time error 1004. Excel is trying to debug the ": ws.Visible = xlSheetVeryHidden"

Please advise, sir.

[Artik]

You need to be sure that the macro will not try to hide all sheets at any given time. Always at least one must be visible!

Private Sub ShowAllSheets()

    Dim ws          As Worksheet


    Application.ScreenUpdating = False


    Worksheets(WelcomePage).Visible = xlSheetVisible


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Data", "Reference": ws.Visible = xlSheetVeryHidden
            Case Else: ws.Visible = xlSheetVisible
        End Select
    Next ws


    Worksheets(WelcomePage).Visible = xlSheetVeryHidden


End Sub

Artik