Implementing workbook level protection

[scroce]

On the close of my workbook, I want to enable workbook level protection in order to keep users from changing the names of my worksheet tabs if they open it with macros disabled.

I tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.Protect ("MyPassword"), Structure:=True, Windows:=False

End Sub


but excel doesn't seem to recognize this command on this event. Does anyone know how to do this?

 

[Richie(UK)]

Hi,

Probably no consolation but ... it worked when I tried it! :wink:

Does it fail all the time or just in certain circumstances?

 

[scroce]

well, that'a fine how-dee doo.....

no, it just doesn't work at all - are you using xl 2002? - i've got some other code in there as well, but I don't think that should impact it. Maybe i'll try it on a new blank workbook.

 

[scroce]

i just tried it on a new workbook with no other code, and it still won't work. It seems that this code doesn't execute on BeforeClose? - I put a msgBox "hello" in there just to test it - i get the msgbox to pop up on close, but the workbook level protection still doesn't work. -

 

[Mudface]

The code is executing but you're not actually saving the change made to the workbook. Try amending it to this: -

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Protect ("MyPassword"), Structure:=True, Windows:=False
ThisWorkbook.Save

End Sub

A better way to do it would be using the Open event: -

Private Sub Workbook_Open()

ThisWorkbook.Protect ("MyPassword"), Structure:=True, Windows:=False

End Sub

 

[scroce]

hi mud - i'll try your suggested code and post back -

.....but if you put that code in the Open event only, it won't work if a user disables your macros when they open the file - isn't that right? This is what i'm trying to defend against.

 

[scroce]

OK - mud that seemed to work - I wasn't aware that you had to call workbook.save in order for that to go into effect. Silly me.

thank you all for your help!!!

sincerely,

scroce

 

[Mudface]

On 2002-09-04 12:37, scroce wrote:

.....but if you put that code in the Open event only, it won't work if a user disables your macros when they open the file - isn't that right? This is what i'm trying to defend against.

True... but then neither will your BeforeClose code run...