[kvinay_00]

Hello,

I am working on VB code in Excel 2003 for getting below result -

(1) After opening the file, it will always go to "First worksheet". This will have a message informing user that macros must be enabled to use this workbook. This sheet will be the only one that is visible if the user doesn't enable macros.

(2) When the user enables macros, the balance sheets automatically shows (gets unhide) and user can use all sheets.

The workbook shall be unusable unless it is opened with macros enabled.

I have tried to write the code many times with my little knowledge on VB but getting errors.

Can you help please?

Thanks in advance

[JBeaucaire]

The most common technique involves hiding all sheets except one during the "save" event. The one sheet that is left visible during the save event is a front sheet that instructs the user to make sure macros are enabled so the other sheets will be made visible/usable when the sheet is opened.

Create a sheet called "Macros" in your workbook. Put a message on that sheet explaining to close the workbook and reopen with macros enabled.

Add ALL of this code to the ThisWorkbook module:

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

(source material)

[kvinay_00]

Hello,

Thank you so much for your reply.

However, this code is showing below error -

Runtime error '1004'
Method 'Visible' of object'_worksheet' failed

Can you look at this please?

Thanks in advance.

[JBeaucaire]

Sure, but I don't see your workbook attached. I can't look into it until you post it.

Click on GO ADVANCED and use the paperclip icon to attach your workbook.

You *DID* name the one sheet "macros" like instructed, right? The one page that remains visible when all the others are hidden?

[kvinay_00]

I have attached a Test Sheet for your reference and eagerly wait for your comments.

I sincerely appreciate your help.

Thank you so much.

[kvinay_00]

Hello JBeaucaire!

Issue resolved, I had earlier kept the code in different sheet. I am sorry for that.

Now it is working fine.

Thank you very much for your help.

[ScottGem]

Just because I'm a stickler for accuracy, I just wanted to point out this is VBA code not VB code. Visual Basic for Applications is a superset of Visual Basic which contains extension to control the specific application. There are very distinct differences between VB and VBA so the two shouldn't be mixed.

[JBeaucaire]

Hello JBeaucaire!

Issue resolved, i had earlier kept the code in different sheet. i am sorry for that.

Now it is working fine.

Thank you very much for your help.

Glad to help. Glad it's working for you. This is a very useful technique. Here's a couple more tips for you:

1. SAVE YOUR USEFUL MACROS:
   
   As you develop macros to do "general" repeatable things, it's good to save them so that you can import them into your sheets any time you need them.
   
   1. Go into the VBEditor
   2. Highlight the ThisWorkbook module with your new working codes in it
   3. Click on FILE > EXPORT FILE
   4. In the popup, create a folder called "CODES" or MACROS"
   5. In the new folder save the macro with a decent descriptive name, like: "ForceMacros(ThisWorkbook).cls"
      
2. HIDE YOUR CODE:
   
   Even though the sheet can still be opened with macros off, a nosy user might open the VBEditor to see why. If the THISWORKBOOK module is visible, they can do damage trying to figure out how to undo it.
   
   1. in the VBEditor, right-click on the VBAProject title in the left pane
   2. Select VBAProject Properties
   3. Click on the Protection tab
   4. Lock the project for viewing and add a password
   5. Save and close your sheet
   6. The next time you open the sheet and VBEditor, the code will not be visible...you will need to enter the password before it will show it to you
      
3. BEWARE OF SAVVY USERS
   The hidden sheets are hidden by macro, so can only be UNHIDDEN by macro. A savvy VB-knowledgeable user can write a macro in another sheet and use it to unhide sheets in yours. Just be aware it is possible... though not probable.
   
   The only 100% sure way to keep everyone from fiddling with your sheets is to not give them sheets, give them PDF printouts.

[kvinay_00]

Hello Friends,

I have added another code as below with aove code. But getting an error of conflict.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myWS As Worksheet
For Each myWS In Worksheets
If myWS.Range("R51").Value = "Check Culprit Code Allocation" Then
Cancel = True
MsgBox "Please check culprit code allocation on " & myWS.Name
End If
Next myWS
End Sub

I am attaching the Test file for reference.

Can you help please?

Thanks a lot in advance!

[JBeaucaire]

It's telling you that you ALREADY have a Worksheet_BeforeSave event macro in the ThisWorkbook module. It's further up. You only get one of these per book.

[JBeaucaire]

I'm presuming the FOR/NEXT part is of primary importance, so I merged the two macros together like so:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myWS As Worksheet
    
    For Each myWS In Worksheets
        If myWS.Range("R51").Value = "Check Culprit Code Allocation" Then
            Cancel = True
            MsgBox "Please check culprit code allocation on " & myWS.Name
        End If
    Next myWS

'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

[kvinay_00]

Thanks a lot for your reply.

I tried to put the combined code but still getting some error. I am attaching the file for your reference.

I would appriciate if you can go thourgh the code and just see the operation on the sheet and you will be able to understand the errors better.

I sincerely thank you for your help.

[kvinay_00]

Sorry, forgot to attach file.

[kvinay_00]

Hello JBeaucaire,

Can you please go through the sheet sent earlier and check the error I am getting?

Thanks in advance.

[JBeaucaire]

Try this one:

[kvinay_00]

Thanks a lot JBeaucaire for your help!

Now it's working fine. One last question, the file is working fine with 'unprotected' workbook. However, when I lock the workbook, I am getting an error.

Would you mind to try the file by protecting workbook?

I am sorry for bothering you so much.

Thanks once again.

[JBeaucaire]

Which sheets? Protected how? Protected to what degree? I imagine these sheets are designed to be "worked with", so you'll need to be more complete in your description of the level of protection on each sheet.

I already know the macros wouldn't work efficiently on protected sheets. That's the kind of thing you start with when planning this stuff out...

[kvinay_00]

It is normal protection available in excel (Tools/ Protection) and not by any macro.

Since I intend to circulate these sheets to various people, I am thinking to protect the workbook just to disable any deletion/ addition etc by the user.

The error is occurring when I protect the workbook.

[JBeaucaire]

Well, you're probably in for a bit of wrangling, you'll most likely need some macro to unprotect your sheets, too. But this is the amended code that protects the sheets during saving. Obviously you don't want it unprotecting automatically, but you'll have to play with how you use your sheet.

Protection + active macros makes for interesting rodeos. I noted the new lines in red.

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)
Dim myWS As Worksheet
    
    For Each myWS In Worksheets
        If myWS.Range("R51").Value = "Check Culprit Code Allocation" Then
            Cancel = True
            MsgBox "Please check culprit code allocation on " & myWS.Name
        End If
    Next myWS

'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).Protect UserInterfaceOnly:=True
Worksheets(WelcomePage).Visible = xlSheetVisible
     
For Each ws In Worksheets
        If Not ws.Name = WelcomePage Then
            ws.Protect , UserInterfaceOnly:=True
            ws.Visible = xlSheetVeryHidden
        Else
            ws.Visible = xlSheetVisible
        End If
    Next ws
     
End Sub
 
Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect , UserInterfaceOnly:=True
        ws.Visible = xlSheetVisible
    Next ws
     
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

[kvinay_00]

Thank you for your reply.

Honestly I am not in wrangling. My be I did not express exactly what I am looking for.

I am attaching the same file which you have edited. I have just added protection to the workbook. Password is "1". Now when you open this workbook with protection ON, you will notice an error.

I wish to protect the workbook so that the users are not able to move/ copy/ delete/ add sheets (just to make it foolproof).

May be you can have a look on the sheet and suggest.

Thanks a lot.