Implementing workbook level protection

scroce

New Member
Joined
Aug 20, 2002
Messages
15
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

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

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

scroce

New Member
Joined
Aug 20, 2002
Messages
15
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

New Member
Joined
Aug 20, 2002
Messages
15
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

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

Code:
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: -

Code:
Private Sub Workbook_Open()

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

End Sub
 

scroce

New Member
Joined
Aug 20, 2002
Messages
15
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

New Member
Joined
Aug 20, 2002
Messages
15
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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...
 

Forum statistics

Threads
1,144,209
Messages
5,723,039
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top