Unprotect Workbook with Function

[Ryan H]

I unprotect and protect all worksheets in a workbook with a User Defined
Function that returns the password.

Sub MyExample()
ActiveSheet.Unprotect Password()
' do stuff to the worksheet
ActiveSheet.Protect Password()
End Sub

Function Password() As String
Password = "AdTech"
End Function

This works great! But for some reason when I try to unprotect or protect my
workbook I get a Run Time Error. I know the password is "AdTech", because I
can manually unprotect and protect the workbook, but VBA doesn't like to do
it thru code.

Sub MyExample()
ThisWorkbook.Unprotect Password()
' do stuff to the worksheet
ThisWorkbook.Protect Password()
End Sub


Any one have any ideas why?

Thanks in Advance!

 

[dan dungan]

Hi Ryan,

I'm wondering how you get the run time error if the function works
great.

Dan

 

[Ryan H]

The function works when I want to unprotect/protect worksheets. But it
doesn't work when I want to unprotect/protect the workbook. I'm not sure
why????

 

[Dave Peterson]

First, I wouldn't use Password for the name of the function. It looks way too
much like the keywords VBA uses. And even though it may not confuse excel, it
surely would confuse me.

Second, I wouldn't include the ()'s in the call to the function.

activesheet.unprotect password:=FuncPWD
ThisWorkbook.Unprotect Password:=FuncPWD

Third, the unmodified code worked fine for me when I ran it--even without my
changes!

So I'm guessing that either the password isn't really what you expect--or
ThisWorkbook isn't the workbook that you expect.