Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Macro's on Password Protected Sheets (97)

    I have a rather complex sheet which is accessed by several different users. Password protecting the sheet is a must to protect the structure. However, I also have 3 different command/macro buttons on the form, that set different parameters based on what type of data is being entered. The problem is when the sheet is "password" protected, the macros will stop running, and I get an error message. How can I "code" past the protection, either through un-locking, running the macros, and re-locking, or in some other similar method.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macro's on Password Protected Sheets (97)

    just disable the protection, before you actually execute your code, then enable it again

    e.g.
    Sub MyMacro()
    ActiveSheet.Unprotect password:="MyPassword"
    'put your code here
    ActiveSheet.Protect password:="MyPassword"
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macro's on Password Protected Sheets (97)

    Unprotect the sheet, do your stuff, then protect it again, like this:

    <pre> Worksheets("MyProtectedSheet").Unprotect ("MyPassword")
    'Do your stuff here
    Worksheets("MyProtectedSheet").Protect ("MyPassword")
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macro's on Password Protected Sheets (97)

    Great. Thanks, it works fine. Second question then. Is it possible to use code to disable the Macro functions under the "Tools" menu, so that the user cannot open the Visual Basic Editor to see the password in the code. Naturally it would be for only this heavily used sheet only.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macro's on Password Protected Sheets (97)

    No, the user would simply press alt-F11 and access your code.

    You will have to lock your project from view. In the VBE: Tools, VBAProject properties, Protection.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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