Protect workbook, save and close

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

Code:

---

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

---

Hi Yasser,

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

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

Mark

may be this will help
Try it

Code:

---

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

---

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!!

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).

Thanks a lot Mr. Mark
Thanks for your great advice
You're right when you told me

Quote:

---

(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

Code:

---

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

---

Quote:

---

Originally Posted by GTO

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