Consulting

Results 1 to 7 of 7

Thread: Protect workbook, save and close

  1. #1

    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

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

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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

  3. #3
    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

  4. #4
    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!!

  5. #5
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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).

  6. #6
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •