+ Reply to Thread
Results 1 to 3 of 3

Sheets locked everytime workbook is opened. Testing if sheets are protected returns FALSE

  1. #1
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

    Sheets locked everytime workbook is opened. Testing if sheets are protected returns FALSE

    Hello,
    This thread is about sheet protection acting weird, Run-time error '1004' and worksheet_open

    I have an annoying problem with protected sheets. I have two simple macros dealing with protection, sheets_lock() and sheets_unlock().
    Basically they just do:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    I also have a Boolean they toggle to monitor if sheets are protected or not, and there's a condition for both that the Boolean must be in the opposite state before running lock or unlock

    Now each time I open the workbook, I made it automatically Call sheets_lock()
    I get an error 1004, saying I can't change a lock-toggle-buttons name because sheets are locked. sheets_lock() shouldn't start at all if sheets are locked.
    It gets more interesting. I added messages to debug this problem. I made a debug message to tel me if activeworkbook.sheets("*").Protectcontents = true or false
    It always tells me it's not protected. I also made a message about whether the variable monitoring protection is TRUE or FALSE. Always says false.
    And the next thing is, the button text cant be changed because it's a protected sheet

    I can provide any additional information or code if needed. Thanks



    Sub sheets_lock:
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,459

    Re: Sheets locked everytime workbook is opened. Testing if sheets are protected returns FA

    Hi there,

    This is difficult to debug properly without access to all of your start-up code.

    The (workbook-level? module-level?) variable "sheets_locked" will always have an initial value of False. If this value is not changed before the "sheets_lock" routine is called then all of the code in that routine will be executed. If the "Tuntikirjaus" worksheet was protected when the workbook was last saved then the "sheets_lock" routine will attempt to make changes to the protected worksheet and an error message will be generated.

    It might be better to write the "sheets_lock" routine along the following lines:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

    Re: Sheets locked everytime workbook is opened. Testing if sheets are protected returns FA

    Hi,
    Thanks for the reply. I realized the variable was useless in there.

    Workbook_open normally just does sheets_lock and then an update sub. I also tried setting the variable first, start with unlock first etc. I don't have much going on in workbook-level, just open and sheets_selection_change which only works when content is protected.
    I have only one module and only 3 subs have to do with sheet protection or the variable: lock, unlock and toggle between them from a button.

    However the problem here is actually that the sheets are always protected when i open the workbook. Not just interface either. I hope I just fixed this when I added .unprotect to workbook_open

    -J

+ 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. Allow TAB for Locked Cells in Protected Sheets
    By FallingDown in forum Excel General
    Replies: 5
    Last Post: 01-05-2018, 09:52 AM
  2. [SOLVED] Problem: Possible to delete locked cells in protected Sheets
    By ongcaps in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-24-2017, 05:10 AM
  3. [SOLVED] VBA to allow users to delete specific rows with locked cells on protected sheets
    By JT12345 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-13-2015, 02:46 PM
  4. Code to delete sheets and rename sheets in a protected workbook?
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-07-2011, 03:02 AM
  5. moving around when sheets are locked (protected)
    By AmyJo in forum Excel General
    Replies: 0
    Last Post: 12-04-2009, 01:57 PM
  6. Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-26-2006, 02:35 PM
  7. Testing an unopened (or opened) worksheet for VBA Project protected
    By Ken Loomis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2005, 08:18 AM

Tags for this Thread

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