Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Protect sheet with VBA Code BUT allow autofilters

  1. #1
    Join Date
    9th August 2006
    Posts
    3

    Protect sheet with VBA Code BUT allow autofilters

    I have used the following code (found on http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm as posted by Dave Hawley) to protect my spreadsheet and still allow my macros to run:

    VB:
    Private Sub Workbook_Open() 
        Dim wSheet As Worksheet 
        For Each wSheet In Worksheets 
            wSheet.Protect Password:="chrisd", _ 
            UserInterFaceOnly:=True 
        Next wSheet 
    End Sub 
    
    
    However this does not allow my autofilters to run. How can I fix this? Thank you!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,846

    Re: Protect sheet with VBA Code BUT allow autofilters

    This is the help for the .PROTECT method.


    Protect method as it applies to the Worksheet object.

    Protects a worksheet so that it cannot be modified.

    expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
    expression Required. An expression that returns a Worksheet object.

    Password Optional Variant. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

    DrawingObjects Optional Variant. True to protect shapes. The default value is False.

    Contents Optional Variant. True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True.

    Scenarios Optional Variant. True to protect scenarios. This argument is valid only for worksheets. The default value is True.

    UserInterfaceOnly Optional Variant. True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

    AllowFormattingCells Optional Variant. True allows the user to format any cell on a protected worksheet. The default value is False.

    AllowFormattingColumns Optional Variant. True allows the user to format any column on a protected worksheet. The default value is False.

    AllowFormattingRows Optional Variant. True allows the user to format any row on a protected. The default value is False.

    AllowInsertingColumns Optional Variant. True allows the user to insert columns on the protected worksheet. The default value is False.

    AllowInsertingRows Optional Variant. True allows the user to insert rows on the protected worksheet. The default value is False.

    AllowInsertingHyperlinks Optional Variant. True allows the user to insert hyperlinks on the worksheet. The default value is False.

    AllowDeletingColumns Optional Variant. True allows the user to delete columns on the protected worksheet, where every cell in the column to be deleted is unlocked. The default value is False.

    AllowDeletingRows Optional Variant. True allows the user to delete rows on the protected worksheet, where every cell in the row to be deleted is unlocked. The default value is False.

    AllowSorting Optional Variant. True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False.

    AllowFiltering Optional Variant. True allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False.

    AllowUsingPivotTables Optional Variant. True allows the user to use pivot table reports on the protected worksheet. The default value is False.

    Remarks
    If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

    If changes wanted to be made to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

    Note 'Unprotected' means the cell may be locked (Format Cells dialog) but is included in a range defined in the Allow Users to Edit Ranges dialog, and the user has unprotected the range with a password or been validated via NT permissions.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

  4. #4
    Join Date
    9th August 2006
    Posts
    3

    Re: Protect sheet with VBA Code BUT allow autofilters

    Dave - When I tried that I get a pop-up asking for the password. I want each page protected by password. What am I doing wrong?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th August 2006
    Posts
    3

    Re: Protect sheet with VBA Code BUT allow autofilters

    Dave - I just was playing around and realized that if I just click cancel on the password pop-up, the macro works. This can do, but I would like to get rid of the pop-up if possible. The sheet MUST be protected as it is the one sheet with all the data that feeds the other 160 tabs.

    Thanks and I have learned how to do things on here that I did not even know I wanted to do!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    29th February 2012
    Posts
    1

    Re: Protect sheet with VBA Code BUT allow autofilters

    Quote Originally Posted by Darth Dennis View Post
    Dave - I just was playing around and realized that if I just click cancel on the password pop-up, the macro works. This can do, but I would like to get rid of the pop-up if possible. The sheet MUST be protected as it is the one sheet with all the data that feeds the other 160 tabs.

    Thanks and I have learned how to do things on here that I did not even know I wanted to do!

    VB:
    Public Sub Protect() 
         ' Protect Sheet
        Sheets("Log").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ 
        , AllowFiltering:=True 
    End Sub 
     
    Public Sub UnProtect() 
         ' Protect Sheet
        Sheets("Log").UnProtect 
    End Sub 
    
    
    Was searching around had exactly the same problem so I created these two public subs in a module so I can call them from anywhere in my sheet.

    then I just add this code in whchever macro needs it(my original macro was protecting/unprotecting multiple sheets and I used this here there and everywhere):

    VB:
    Call protect 
    Call unprotect 
    
    
    Hope this helps if anyone else with he same problem.

    James.

    FYI using Excel 2010

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Protect sheet with VBA Code BUT allow autofilters

    Welcome to Ozgrid. The code below will allow filtering and allow Macros to make changes.

    VB:
    ActiveSheet.Protect Password:="Xyz", DrawingObjects:=True, _ 
    contents:=True, Scenarios:=True, AllowFiltering:=True,_ 
    userinterfaceonly:=True 
    
    
    Also see: Enable AutoFilter functionality for a protected worksheet If all else fails, record a Macro protecting the sheet and check Allow AutoFilters

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Protect Sheet But Allow Macro Code To Modify
    By Ares in forum EXCEL HELP
    Replies: 4
    Last Post: May 24th, 2008, 10:25
  2. Unique Count With AutoFilters Macro Code
    By CesarF in forum EXCEL HELP
    Replies: 1
    Last Post: May 8th, 2008, 15:22
  3. Remove AutoFilters Code
    By MikeBrough in forum EXCEL HELP
    Replies: 4
    Last Post: September 19th, 2007, 16:04
  4. Replies: 2
    Last Post: September 23rd, 2004, 22:27
  5. Protect Sheet vs. VBA Code
    By steveski in forum EXCEL HELP
    Replies: 1
    Last Post: February 19th, 2004, 11:05

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
  •  
porno