PDA

View Full Version : Protect workbook, save and close



YasserKhalil
07-08-2010, 01:58 AM
I have a workbook which I want to protect before closing it
using password 1 for example then save the workbook and finally close it

I have a code but needs to be refined - I think - as I got error


Application.DisplayAlerts = False
ThisWorkbook.Password = "1"
ThisWorkbook.Save
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
ThisWorkbook.Close

GTO
07-08-2010, 02:38 AM
Hi Yasser,

This works for me:
Sub tmp()
ThisWorkbook.Password = "1"
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Where (what module) do you have the code currently?

Mark

Yahya Hussain
07-08-2010, 02:41 AM
may be this will help
Try it

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
With ThisWorkbook
.Save
.Protect "1"
.Close
End With
End Sub

YasserKhalil
07-08-2010, 02:51 AM
Nice Mr. Yehya
It works fine with me
But there's a problem in your code ..
You neglected the line
Application.DisplayAlerts = True
And this I think will cause problem with Excel alerts

The problem now :: When I added this line at the end of the code , the problem appears again!!
Microsoft Excel has encountered problem (Send Error Report -- Don't Send)

Is the version of excel 2007 the reason or the windows version SP2 or what I'm disappointed!!

GTO
07-08-2010, 03:04 AM
Hi again,

You should not need to be turning .DisplayAlerts off or on, as you should not be getting any alerts.

Also, you need to set the password before saving the workbook, as changing the password is a change to the file, and thus, marks it un-saved.

I would add that I asked about where the code was, as I am curious if you are running this from a button or such, where the user has a choice - or whether it is being run automatically. If it is being run automatically, for what it's worth, forcing a save can have undesirable results if the user glitched something and just wants out (w/o saving the horrid mess they just made).

YasserKhalil
07-08-2010, 03:37 AM
Thanks a lot Mr. Mark
Thanks for your great advice
You're right when you told me
(you need to set the password before saving the workbook, as changing the password is a change to the file, and thus, marks it un-saved)

But the problem appears again

Finally it disappeared when I changed the Property "Password" and used "Protect"
The code that works fine now


Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
.Protect 1
.Save
End With
End Sub

Yahya Hussain
07-08-2010, 04:37 AM
Hi again,

You should not need to be turning .DisplayAlerts off or on, as you should not be getting any alerts.

Also, you need to set the password before saving the workbook, as changing the password is a change to the file, and thus, marks it un-saved.

I would add that I asked about where the code was, as I am curious if you are running this from a button or such, where the user has a choice - or whether it is being run automatically. If it is being run automatically, for what it's worth, forcing a save can have undesirable results if the user glitched something and just wants out (w/o saving the horrid mess they just made).
yes u r right GTO