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

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 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=379))

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.

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?

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

I sincerely appreciate your help.

Thank you so much.

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.

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.

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:

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.
Go into the VBEditor
Highlight the ThisWorkbook module with your new working codes in it
Click on FILE > EXPORT FILE
In the popup, create a folder called "CODES" or MACROS"
In the new folder save the macro with a decent descriptive name, like: "ForceMacros(ThisWorkbook).cls"


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.
in the VBEditor, right-click on the VBAProject title in the left pane
Select VBAProject Properties
Click on the Protection tab
Lock the project for viewing and add a password
Save and close your sheet
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


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.

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!

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.

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

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.

Sorry, forgot to attach file.

Hello JBeaucaire,

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

Thanks in advance.

Try this one:

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.

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

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.

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

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.

Honestly, at this point, I point people away from Excel. To provide spreadsheet information to OTHERS for review only, I do not send spreadsheets. I send PDFs. PDFs can be read by anyone anywhere and cannot be edited in any way. They are secure and simple.

I use a free PDF tool called PrimoPDF (PrimoPDF.com) that installs as a printer driver so I can make PDFs instantly from inside any program. PrimoPDF includes password protection and print suppression... all the things you would want from a PDF utility.

Our company stopped sending Excel files around years ago and we haven't been sorry, not once.