Results 1 to 5 of 5

Thread: Protecting a Sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Protecting a Sheet

    Hi all,

    I have a spreadsheet with macros that I wrote. I have to give it to some colleagues and need to protect the sheet. When I protect it normally it makes the code not work. Has anyone protected a macro enabled worksheet?

    Thanks

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting a Sheet

    Write some lines into your code, to un-protect the sheet, then re-protect, after the code's run:
    Code:
    Sub unprotect_sheet()
    Me.Unprotect "password"
    ' All your code here...
    Me.Protect "password"
    End Sub
    Obviously change the password to the one you've used, and if you don't want your users having a sneaky peek into your code, to find the hard-coded sheet's password, then password-protect your project, too.

    The "Me" keyword will only work if you put the code into the actual sheet's code module. If the code's anywhere else, you'll need to refer to the sheet longhand "Sheets("Sheet1") or "Activesheet" etc.
    Last edited by sykes; Jul 22nd, 2019 at 12:12 PM. Reason: Me keyword.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting a Sheet

    Quote Originally Posted by ERed1 View Post
    Hi all,

    I have a spreadsheet with macros that I wrote. I have to give it to some colleagues and need to protect the sheet. When I protect it normally it makes the code not work. Has anyone protected a macro enabled worksheet?

    Thanks

    Is your code going to affect the protected sheet? If so, you will need to programmatically unprotect it prior to your code running and then programmatically re-protect it after code execution.

  4. #4
    Board Regular
    Join Date
    Jun 2019
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting a Sheet

    That makes sense. Thank you for the code to do it, it worked.

  5. #5
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting a Sheet

    You're welcome. Thanks for the feedback.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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