Results 1 to 7 of 7

Protect Worksheets DrawingObjects Not Working Correctly

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Protect Worksheets DrawingObjects Not Working Correctly

    Hi,
    Excel 2013 VBA - I have this code and it appears to be working as a toggle, which is not right (?)

    If Sheet01 is unprotected and I run the code below it becomes protected - you cannot type in cells that are locked

    If I run the code again (no other changes), the previously protected Sheet01 become unprotected - you can type in cells that are locked. Also, the sheet still appears to be protected because clicking on "Unprotect Sheet" in the Review menu requires a password. (The menu icon shows as Unprotect versus Protect, even though the sheet is now unlocked.)

    It seems to be toggling between protected and unprotected. Do I need to add something to this code to check if it is locked/unlocked? I've been using this code for years and never found this problem before.

        Dim WS As Worksheet
            For Each WS In ActiveWorkbook.Worksheets
                    WS.Protect Password:="xxx", UserInterfaceOnly:=True, DrawingObjects:=False
            Next WS
    I tried it again in a completely clean workbook and it does the same thing.

    EDIT: If I list each sheet individually, the problem does not occur:
    Sheet1.Protect Password:="xxx", UserInterfaceOnly:=True
    Sheet2.Protect Password:="xxx", UserInterfaceOnly:=True
    Sheet3.Protect Password:="xxx", UserInterfaceOnly:=True
    AHA - it seems to toggle only if DrawingObjects is included
    Sheet1.Protect Password:="xxx", UserInterfaceOnly:=True, DrawingObjects:=False
    Sheet2.Protect Password:="xxx", UserInterfaceOnly:=True, DrawingObjects:=False
    Sheet3.Protect Password:="xxx", UserInterfaceOnly:=True, DrawingObjects:=False
    I want users to be able to add comments, which is why DrawingOjbects is included.
    Last edited by ker9; 06-05-2019 at 03:14 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Protect worksheets - VBA filters not working
    By jh9940 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-17-2022, 01:25 PM
  2. [SOLVED] VAB/Macro to unhide, unprotect, copy to other worksheets, protect & hide worksheets again
    By Roma1r in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-08-2018, 08:26 AM
  3. Replies: 1
    Last Post: 08-30-2017, 02:32 AM
  4. VBA SQL not working correctly?
    By chaddug in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2015, 03:45 PM
  5. Replies: 9
    Last Post: 03-08-2013, 11:50 AM
  6. if not working correctly
    By sandos in forum Excel General
    Replies: 3
    Last Post: 12-12-2011, 01:03 AM
  7. CSE Sum-if not working correctly
    By krjohnso in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2009, 05:08 PM

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