+ Reply to Thread
Results 1 to 8 of 8

ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

  1. #1
    Registered User
    Join Date
    01-25-2016
    Location
    Ottawa, Canada
    MS-Off Ver
    2010
    Posts
    7

    ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    Hi,

    I have an driver activity worksheet with days of the week (Sun - Sat) arranged like this.

    Mon
    ---
    ---
    ---
    ---

    Tue
    ---
    ---
    ---
    ---

    Wed
    ...

    and so on. The weekdays are groups and expand and collapse with buttons for each day plus a "Show All" button. Works.

    I have to protect it because the dimwits are modifying the worksheet to suit how they feel that particular day. Once they do, we can't read them programmatically.

    I use
    Please Login or Register  to view this content.
    in Workbook_Open to open the current day. Also works fine.

    When I add this to the weekday buttons (that collapse and expand the appropriate days)
    Please Login or Register  to view this content.
    It seems like the ActiveSheet.Protect gets executed before the rows have a chance to expand/collapse.

    I can use the "Show All" button and ONE extra button, then all subsequent buttons don't execute. It's as if ActiveSheet.Unprotect isn't unprotecting.

    Any takers?

    Example attached:
    Attached Files Attached Files
    Last edited by Proventus; 02-02-2016 at 08:36 PM.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    If you attach a sample workbook with what you have including the code(s), button(s) you are using, I'll give it a shot.
    Is your
    Please Login or Register  to view this content.
    After any Dim lines and before the actual code that you want to run? Based on the sample code you provided I would say yes, but just asking to be sure.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    Well, it's to be hoped that none of the dimwit drivers or staff working for a company on Ottawa are interested in Excel

    However, for us dimwits who are interested in Excel to do anything to help you, you're going to need to provide a lot more information ... like code and a workbook, maybe.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    Could the code that's not been posted be the problem?

    Perhaps even the action of unprotecting a sheet could cause problems as it might cause the sheet being unprotected to become active?
    If posting code please use code tags, see here.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    @Norie: why would unprotecting a sheet make it active?

  6. #6
    Registered User
    Join Date
    01-25-2016
    Location
    Ottawa, Canada
    MS-Off Ver
    2010
    Posts
    7

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    I've added the example.

  7. #7
    Registered User
    Join Date
    04-22-2013
    Location
    Philippines & Australia
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    73

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    When Protecting the sheet Try using the Following

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-25-2016
    Location
    Ottawa, Canada
    MS-Off Ver
    2010
    Posts
    7

    Re: ActiveSheet.Unprotect("pass") / ActiveSheet.Protect ("pass") breaks functionality

    Quote Originally Posted by Catman50 View Post
    When Protecting the sheet Try using the Following

    Please Login or Register  to view this content.

    Works like a charm. Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Remove deeply integrated "Activesheet"Protect Password VBA
    By excel2425 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2015, 03:33 PM
  2. [SOLVED] How can I add "If ActiveSheet.Name = "Sheet2" Then" to the following code in a module ?
    By omega0010 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2014, 11:57 PM
  3. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  4. Unable to run code in between "Unprotect" and "Protect" object.
    By eric1234 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-28-2013, 12:44 PM
  5. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  6. Replies: 3
    Last Post: 03-08-2013, 09:28 AM
  7. [SOLVED] Display , "PASS"," MERIT" or "DISTINCTION"
    By Integrity in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 05-01-2012, 05:41 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1