Password issues on Workbook Open Event

[quackdad]

When I run this code I get a message that the password is incorrect. But it is correct. Do I need to state it another way?

Private Sub Workbook_Open()
    Sheets("Employees").Unprotect Password = "Password"
    Range("F8").Select
    Sheets("Employees").Protect Password = "Password"
End Sub

 

[RoryA]

The latter won't work.

You mean:

.Unprotect "Password"

There are two ways of passing arguments to routines - by name (Password:="Password") and by position (just "Password'). If you pass by name, you can pass the arguments in any order you like, and only include the ones you require; if you pass by position, you must pass in the order the routine expects them, and you must leave a gap for any optional ones you do not wish to pass.

With a method like Unprotect there is no real difference because there only is one argument - the password.

However, with a method like Workbooks.Open, there are 15 possible arguments, only the first of which is required - the filename. Of course if that's all you want to pass, there's no great benefit to passing by name, other than perhaps more legible code:

Workbooks.Open "C:\book1.xls"

versus:

Workbooks.Open FileName:="C:\book1.xls"

If you want to provide a password, however, which is the fifth argument, passing by name is still just:

Workbooks.Open FileName:="C:\book1.xls", Password:="blah"

whereas by position, you have to leave gaps for the optional arguments 2-4:

Workbooks.Open "C:\book1.xls", , , , "blah"

Not too bad, but let's say I want to open the workbook read-only and tell it what to do if it encounters an error while trying to load the workbook data. Using named arguments:

Workbooks.Open FileName:="C:\book1.xls", Password:="blah", ReadOnly:=True, CorruptLoad:=xlRepairFile

but passing by position it's:

Workbooks.Open "C:\book1.xls", , True, , "blah", , , , , , , , , , xlRepairFile

I know which one I think is easier to write - and to read 6 months later!

(I think I just drafted this week's blog entry )