+ Reply to Thread
Results 1 to 5 of 5

Unprotecting/Protecting via VBA

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Merseyside
    MS-Off Ver
    Office 2010
    Posts
    38

    Unprotecting/Protecting via VBA

    I am trying to have a VBA where it unprotects the sheet, does what it needs to do then protects the sheet again.

    When it reprotects the sheet it limits the user back to "select locked/unlocked cells"

    could someone please let me know what I need to put so that it puts the same level of protection on?

    Sub Chrisbrough2()
    Sheets("Filter - non relevant ").Unprotect Password:="Cloud"
    With Sheets("Filter - non relevant ")
    .Range("E5:E" & .Range("A" & Rows.Count).End(3).Row).AutoFilter 1, "NO"
    .Range("A6:D" & .Range("A" & Rows.Count).End(3).Row).SpecialCells(12).Copy Sheets("Step 2 - Filtered (Long List)").Range("A" & Rows.Count).End(3)(2)
    .AutoFilterMode = False
    Sheets("Filter - non relevant ").Protect Password:="Cloud"
    Sheets("Step 2 - Filtered (Long List)").Select
    End With
    End Sub

  2. #2
    Registered User
    Join Date
    09-10-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unprotecting/Protecting via VBA

    Hi,
    You could use one macro for protection that include your settings and another one to unprotect. For instance:

    Sub ProtectSheet()
    With Sheet1
    .Unprotect
    .Cells.Locked = False
    .Range("A1").CurrentRegion.Locked = True
    .Protect , userinterfaceonly:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    End With
    End Sub

    Sub UnprotectSheet()
    Sheet1.Unprotect
    End Sub

  3. #3
    Registered User
    Join Date
    08-12-2014
    Location
    Merseyside
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Unprotecting/Protecting via VBA

    I was hoping that the macros I have in place would reapply my protection as it will be the users who will be using the macro above so didn't want to add another macro to reprotect the sheet if i can get it all in1 go.

    is that possible?

    Thanks

  4. #4
    Registered User
    Join Date
    09-10-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unprotecting/Protecting via VBA

    Upto you, you can put all in a single macro. Key point is that you define how you want to protect the sheet (determine the locked range)

  5. #5
    Registered User
    Join Date
    08-12-2014
    Location
    Merseyside
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Unprotecting/Protecting via VBA

    Would I just put what you have said at the bottom of my macro when it is to be protected again?

    I'm not very good at macros/VBA so will need spelling out to me haha

    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. protecting and unprotecting
    By xe-dingo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2010, 04:47 AM
  2. Protecting/Unprotecting
    By ExcelNut in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2010, 03:24 PM
  3. protecting and unprotecting with VBA
    By soma104 in forum Excel General
    Replies: 2
    Last Post: 02-06-2010, 02:17 PM
  4. Protecting and unprotecting a cell
    By bgrondines in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-08-2010, 04:25 PM
  5. Protecting & Unprotecting Sheets
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-10-2005, 05:06 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