workbook password 2

[Epsilon101]

Hi, could someone point me in the right direction to the best simplest way to password protect an excel workbook so that a password has to be entered before going to the first worksheet, whether someone enables macros or not.

I have used excel security to protect the workbook from creating new sheets and the usual, and protected and hidden sheets also protected the vbe.

None of the data is very important, just want to have only certain people using it.

I was thinking at first of a pop up form with a text box with a global variable as the password, except that will not work with macros disabled.


---------------------------------------

Neil

 

[byundt]

This request is related to the general problem of forcing users to enable macros. John Walkenbach suggests one way at

http://j-walk.com/ss/excel/tips/tip100.htm

You basically make all the worksheets but one xlVeryHidden. The one worksheet is a splash screen that says something to the effect of "You're not going to see anything until you enable macros". If macros are enabled, then the splash screen is hidden, and all the other worksheets are made visible. It's at this point that you could ask for a password.

You then need to reverse the procedure every time the workbook is saved, then make the sheets visible once again. Or alternatively, reverse the procedure when the workbook is closed, saving the file as part of the process. I prefer the first alternative.

 

[byundt]

Here is some code that embodies Walkenbach's suggestions. I've modified it so Sheet1 won't display unless the user enters the correct password. You may want to modify that bit to give the user more than one chance to enter a valid password.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ThisWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Dim Pswd As String
Pswd="myPassword"
Application.ScreenUpdating = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then 
       If ws.Name="Sheet1" Then
           If InputBox("Please enter your password")=Pswd Then ws.Visible=xlSheetVisible
       Else
           ws.Visible = xlSheetVisible
       End If
   End If
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

 

[Epsilon101]

Hi sorry for the wait, thanks for your help on this.

Can i ask for some pseudocode on what these subs are doing.
I am missing something more than likely.
I tried using it in my existing workbook, i added a splash screen worksheet and hid my menu sheet, disabling macros let me in, enabling opened and asked for a password and i entered one and the input box gave an error. My workbook password is on so it looks to me that is whats causing problems, but the debug also complained that it didnt know what xlSheetVeryHidden is??

I have added a password to my workbook via save as> tools> so it asks for a password before opening but i still need to have a splash screen appear if they disable macros.

---------------------------------------

Neil

 

[byundt]

Neil,
Which version of Excel are you using? I'd like to retest the code in the same version that you use.
Brad

 

[GlennUK]

Can't you just have an Excel open password? Why let the unwanted users even open the file? You set a password to open on the Save As/Tools/General Options box.



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[Epsilon101]

Glenn: Yea recently added that password to my workbook, works great just havent got a different sheet to pop up if someone doesnt enable macros.
Brad: I am using Excel 2002 in windows xp pro.

The setup i have is:

User opens workbook: enters the password to get in.
All sheets are hidden and protected apart from the first 'Menu' sheet.
The workbook is also protected so people dont add a new worksheet.
I have got 4 buttons in the menu.
-1 'Open input form'
-2 'Maintain combo lists'
-3 'Export data to separate workbook'
-4 'Save workbook'

The sheets are 'Menu','Data','Titles','Introducer','Reason','PassedTo'
Button 1 opens a userform with 17 textboxes, 4 combo boxes, 2 check boxes, ive got 6 text boxes as required fields and 1 combo box required.

Sheets Titles, introducer, reason, passedto hold the data that is used in the combo boxes, they are hidden and protected.

Button 2 goes to a userform to maintain these sheets without being able to go into them, so the user selects the combobox to edit using radio buttons and a listbox shows whats in there, they then add or delete what they want to.

Since these are all protected writing to them required to unprotect and protect again.

Button 3 - This exports the info in 'Data' sheet, which is just transactions from the first userform. This sheet is hidden and protected, i have used the copy property of the sheet in vba to move this to a new workbook, but to do it i had to unprotect and unhide it and then protect and hide again.

Button 4 - This just saves the workbook if they havent already saved, just so its there infront of them, because if they add a transaction or change the cbo box tables and dont save, you get the idea...

Well thats what i have setup.

Brad im just not sure how to set your code up to work correctly thats why i wanted to see it in psuedo, thanks for any further help from anyone.

---------------------------------------

Neil

 

[byundt]

Neil,
I'll take a look at the complications caused by your situation later today, but here is a sample workbook that requires you to enable macros to open it:

http://home.mchsi.com/~byundt/ForcedEnableMacros.xls

The macros are slightly different than posted, but it's the same theme.
Brad

 

[byundt]

I revised the test workbook I posted at

http://home.mchsi.com/~byundt/ForcedEnableMacros.xls

It is now protected with the password "Brad"

Here's the code with comments:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This sub runs when the user tries to close the file
wbClosing = True    'Set the Boolean flag wbXClosing to True. This value is used by SpecialSave sub.
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This sub runs when the user tries to Save or Save As the file
SpecialSave     'Call the SpecialSave sub
Cancel = True   'Since SpecialSave sub will save the workbook, don't try to repeat it a second time
End Sub


Private Sub SpecialSave()
'This sub runs whenever user tries to save the file
Dim ws As Worksheet, wsSplash As Worksheet, wsCurrent As Worksheet
Dim pswd As String
pswd = "Brad"   'Password to protect the workbook
Application.ScreenUpdating = False  'No screen flicker. Code runs faster if screen doesn't update until macro done
Application.EnableEvents = False    'Stop events handling until end of sub. This prevents recursive calling of event subs.
Set wsSplash = Worksheets("Splash screen")  'Pointer to the Splash screen worksheet
Set wsCurrent = ActiveSheet 'Pointer to the current worksheet
ThisWorkbook.Unprotect Password:=pswd   'Unprotect the workbook using the password stored in string variable pswd
wsSplash.Visible = xlSheetVisible   'Make the Splash screen worksheet visible
For Each ws In ThisWorkbook.Worksheets  'Loop through all the worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden   'Make the other worksheets very hidden (only visible through VBA code)
Next ws
ThisWorkbook.Protect Password:=pswd     'Protect the workbook before saving it
ThisWorkbook.Save   'Save the file
If wbClosing = True Then ThisWorkbook.Close 'If trying to close the workbook, allow the process to occur
ThisWorkbook.Unprotect Password:=pswd   'Unprotect the workbook. Will get error when trying to make sheets visible if this not done.
For Each ws In ThisWorkbook.Worksheets  'Loop through each worksheet
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible  'Make all sheets except Splash screen visible
Next ws
wsSplash.Visible = xlSheetVeryHidden    'Make Splash screen very hidden
wsCurrent.Activate  'Activate the initial worksheet
ThisWorkbook.Protect Password:=pswd 'Restore workbook protection
Application.EnableEvents = True 'Turn events handling back on
Application.ScreenUpdating = True   'Allow the screen to update
End Sub

Private Sub Workbook_Open()
'This sub runs when the workbook opens
Dim ws As Worksheet, wsSplash As Worksheet
Dim pswd As String
pswd = "Brad"   'Password used to protect the workbook
Application.ScreenUpdating = False  'Turn screen updating off. Sub runs faster. No screen flicker.
Application.EnableEvents = True     'Turn events handling off. Avoids recursive calling of events subs
wbClosing = False   'Initialize wbClosing to False
Set wsSplash = Worksheets("Splash screen")  'Pointer to the Splash screen worksheet
ThisWorkbook.Unprotect Password:=pswd       'Unprotect the workbook
wsSplash.Visible = xlSheetVisible   'Make the Splash screen worksheet visible
For Each ws In ThisWorkbook.Worksheets  'Loop through all the worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible  'Make worksheets visible
Next ws
wsSplash.Visible = xlSheetVeryHidden    'Make the Splash screen worksheet very hidden (only visible through VBA code)
ThisWorkbook.Protect Password:=pswd     'Protect the workbook with a password
Application.ScreenUpdating = True   'Allow the screen to update
End Sub

 

[Epsilon101]

Thanks for the descriptions Brad, useful bit of code, star for you.

The very hidden property opens up a few new doors. I just need to ask, is the opposite of xlVeryHidden just xlSheetVisible or is it something else, because i need to write to the sheets and copy one of them, need to make sure i can do that if they are very hidden. I probably wouldnt need to ask this by tomorrow, just i havent tested this code out as yet.


---------------------------------------

Neil