Hi,

Too late, I found by myself.

Thanks anyway Rudi

Share!
I use protection under the tools menu to protect the contents of some cells and leave others open (protect sheet and protect workbook). However, while hiding formulae and write protecting selected cells, the file can still be opened and viewed by anyone even though they can only change or enter data in the selected unprotected cells.
So to close out this thread, it would be nice to know the steps to prevent the workbook being opened in the first place without a password.

Just use:

File|Save As|Options|General Options|Password To Open

Regards,

Brian

There is one very important thing to remember.  You can download utilities that allow you to hack the edit protection passwords and the file-open protection passwords.

Nothing you put in an Excel spreadsheet is completely safe from someone who really wants to get at it.

Brad

Brad,
You're absolutely right, I've used those same utilities to crack a forgotten cell protection password (which are essentially equivalent to 4 digits, no matter how many digits you use),but I believe a long file open password would still be the safest bet, as the the time to crack can still be years if chosen randomly enough - please correct me if I'm under a misapprehension
Ian

I sometimes type passwords in a selected cell.  Pic a cell that means something and is the far lower right of the sheet.  For example if your name is Ivan Smith and your were born in 1975 put the password in cell IS1975.  Change the font color to white and not many people will see it.

Now, what if someone (like me) presses Ctrl-Enter in your sheet and ends up in IS1975, and looks in the Formula bar to see what's in the cell? He/She knows your password without even having to have one of them cracking tools! You'd be probably even safer publishing your password list on your homepage...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Hello,

Thanks to all it's was very interesting. Since you were talking about cracking tools, were can I find those utilities.
Not to worry I just want the try them out it could be come in handy in the near future.

Rudi

Rudi:

Go to:

http://www.straxx.com/excel/password.html

This is an excellent Excel Password remover.  I have used for maybe 3 years and have never had any problems with it - except maybe the popup reminder to upgrade.  It's shareware, so you can use it for trial free and pay later if you like it and want to subscribe.

I protect all my engineering worksheets because I can't trust myself (& others) to not make a typo or mechanical mistake in using them - especially large, detailed datasheets.  I believe any Excel password can be broken - as this program proves; but the important thing is to get protection from your everyday, clumsy mistakes - and this system seems to work very well for me and gives me peace of mind.

Art Montemayor
Spring, TX

I heartily agree with Montemayor on the matter of using passwords to protect spreadsheets from clumsy error.  In fact, I take his practice a bit further.  I use the same password on all my engineering spreadsheets, and I have written a VBA procedure to apply it to all worksheets in the active workbook, and another procedure to unlock all the worksheets.  Then I assigned shortcuts to the procedures.  So when I want to meddle in a spreadsheet I merely type Ctrl-Shift-U (for unprotect), and at the end of my meddling I merely type Ctrl-Shift-P (no prize for guessing).  This latter procedure also reminds me what the hard-wired password is, just for the record.

Whilst I try to keep the password to myself, I am not going to lose any sleep if it "escapes".  I know that some purists out there will be horrified at this approach, but is has worked for me for quite a few years now.

HTH

Denial:

I see you have done your spreadsheet protection in the proper, engineering manner - you've automated the application.  I envy your ingenuity and resourcefulness and I would ask you to share your VBA code (macro?) so I can start applying it myself.  That is definitely the most efficient way to get what I need.  My email is artmontemayor37@hotsheet.com.  Can you send me a copy?  Thanks.
Regards

Art Montemayor
Spring, TX

For what it's worth, here's the VBA listing for the two complementary macros.  I believe that you should be able to cut&paste directly into a VBA module, even though the line wrapping below looks ugly.

Note that you will have to edit in your chosen password in two places.  You will also have to create the necessary shortcuts (if you want them).  Finally, if you did it all in an otherwise empty spreadsheet, you can convert it all to an add-in, which will make life a bit easier when you actually come to use the macros.

------  Macros begin  ------
Option Explicit
Option Base 1
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
Sub Protect_All()
'
'  Macro to apply a hardwired password to all sheets in a workbook and to the workbook itself.
'
Dim WorkSht As Worksheet, NumbSheets As Integer
Dim PassWd, Ans, ShtName As String, StartShtName As String
Const Descr As String = "Macro to protect all worksheets"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to protect all sheets in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Set the "hard-wired" password.
'
PassWd = "PutYourPasswordHere"
'
'  Record the presently-active sheet, so we can return to it when finished.
'
StartShtName = ActiveSheet.Name
'
'  Loop through all the worksheets.
'
NumbSheets = 0
For Each WorkSht In Worksheets
    WorkSht.Activate
    ShtName = ActiveSheet.Name
    On Error GoTo P_Failure
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PassWd
    ActiveSheet.EnableSelection = xlNoRestrictions
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Now protect the workbook.
'
ShtName = "Workbook's structure"
On Error GoTo P_Failure
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=PassWd
On Error GoTo 0
'
'  Return whence we starteth-ed, then it's all over.
'
Worksheets(StartShtName).Activate
MsgBox "All done OK  (" & NumbSheets & " sheets)." & Chr(13) & Chr(13) & _
       "Password used was """ & PassWd & """." & Chr(13) & Chr(13) & _
       "Take care not to forget it.", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
P_Failure:
MsgBox "Protection attempt failed for """ & ShtName & """ so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
Sub Unprotect_All()
'
'  Macro to unprotect all sheets in a workbook, and the workbook itself.
'  It assumes that all these protections have been set with the same password.
'
Dim WorkSht As Worksheet, NumbSheets As Integer, Ans
Dim PassWd, ShtName As String, StartShtName As String
Const Descr As String = "Macro to unprotect all worksheets"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to unprotect all sheets in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Set the "hard-wired" password.
'
PassWd = "PutYourPasswordHere"
'
'  Record the presently-active sheet, so we can return to it when finished.
'
StartShtName = ActiveSheet.Name
'
'  Loop through all the worksheets.
'
NumbSheets = 0
For Each WorkSht In Worksheets
    WorkSht.Activate
    ShtName = ActiveSheet.Name
    On Error GoTo U_Failure
    ActiveSheet.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Now unprotect the workbook.
'
ShtName = "Workbook's structure"
On Error GoTo U_Failure
ActiveWorkbook.Unprotect Password:=PassWd
On Error GoTo 0
'
'  Return whence we starteth-ed, then it's all over.
'
Worksheets(StartShtName).Activate
MsgBox "All done OK  (" & NumbSheets & " sheets).", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
U_Failure:
MsgBox "Unprotection attempt failed for """ & ShtName & """ so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub
------  Macros end  ------

Happy passwording.

Is it possible to crack the password which protects the VB code?

Yes, but it is more difficult.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.