+ Reply to Thread
Results 1 to 3 of 3

Sheet Locking and properties

  1. #1
    Registered User
    Join Date
    06-14-2018
    Location
    UK
    MS-Off Ver
    2017
    Posts
    14

    Sheet Locking and properties

    Hi All,

    I am using this quick macro below to lock all sheets in the workbook with the same password. However, I also want to ensure every sheet has .AllowFormattingRows to be true. For some reason, whenever I run the macro, it locks the sheet fine, but always sets the AllowFormattingRows to false, even if it was true to start with.

    Any ideas please?


    Dim myWS As Worksheet
    Dim myPW As String

    myPW = InputBox("Enter Password", "Protect All")
    If myPW > "" Then
    For Each myWS In Worksheets
    myWS.Select
    If myWS.Protection.AllowFormattingRows = False Then
    myWS.Protect AllowFormattingRows = True
    End If
    If myWS.Protection.AllowFormattingRows = True Then
    myWS.Protect AllowFormattingRows = True
    End If
    myWS.Protect Password:=myPW
    Next myWS
    End If

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,798

    Re: Sheet Locking and properties

    I don't see much point in the If test since you do the same thing either way. Just use:

    Please Login or Register  to view this content.
    Rory
    I drink, and I know things

  3. #3
    Registered User
    Join Date
    06-14-2018
    Location
    UK
    MS-Off Ver
    2017
    Posts
    14

    Re: Sheet Locking and properties

    uh so simple. Thank you!

+ 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. Replies: 0
    Last Post: 05-29-2013, 10:28 AM
  2. Sheet Name Properties
    By DCSwearingen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2008, 06:28 PM
  3. Replies: 1
    Last Post: 07-21-2006, 11:05 AM
  4. Sheet properties Name and (Name)
    By nsv in forum Excel General
    Replies: 2
    Last Post: 01-24-2006, 06:56 AM
  5. [SOLVED] Sheet tab properties
    By marko in forum Excel General
    Replies: 7
    Last Post: 12-11-2005, 11:25 AM
  6. Sheet tab properties
    By marko in forum Excel General
    Replies: 7
    Last Post: 12-11-2005, 11:25 AM
  7. [SOLVED] Properties transferring from excel cells to word file properties
    By lubo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2005, 07:05 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