Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Macro To Protect Sheet But Allow AutoFilter & Macros

I have a file that has an autofilter set to a specific data range.

I have then protected the worksheet using the following code:


	VB:
	
 SheetProtect() 
    Dim msheet 
    msheet = ActiveSheet.Name 
    With Worksheets(msheet) 
        .Protect Password:="Xyz", DrawingObjects:=True, _ 
        contents:=True, Scenarios:=True, _ 
        userinterfaceonly:=True 
         
        .EnableAutoFilter = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything works fine.

However, after I save the file and exit and then reload the file, the autofilter fails. The autofilter drop down arrows are still there but the feature appears frozen. The rest of the worksheet works normally.

If I unprotect the worksheet manually and protect it again using the above code, it works normally.

Any insight into this situation would be appreciated.

Thank you.


Post your answer or comment

comments powered by Disqus
Thanks again everyone for this useful forum.

I have encountered yet another problem with Excel/VBA. I am uncertain how to properly write protect/unprotect code. Essentially I have one sheet with an entry form and a second sheet where the data is stored. So far the basic solution I have come up with is to have my macro turn off protection when trying to enter the data and turn it back on after it executes but that seems kind of poor and doesn't solve this latest issue.

The main issue is that I want the end user to be able to sort the entered data but not be able to modify the data its self. I know this is possible when enabling protection within the worksheet. You select from many check boxes how you would like the sheet protected but I don't know the equivalent values to these boxes in VBA code.

I read it was possible to have cells protected to user interface but not macro commands but I haven't been able to get that to work properly either.

Any help would be appreciated. Thank you

I have an event macro Workbook_Open() that contains:

For Each wSheet In Worksheets
wSheet.Protect Password:="xxxxxx", _
UserInterFaceOnly:=True, _
AllowUsingPivotTables:=True, _
AllowSorting:=True
Next wSheet

This is to protect the worksheets. I have a sheet with a summary pivot table
and have assigned a macro to a button to refresh the pivot with

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

This fails due to the protection, I assumed the UserInterFaceOnly:=True
would allow the macro to run OK. I get run-time error 1004 and a message
"That command cannot be performed while a protected sheet contains another
PivotTable report based on the same source data." "To remove protection from
the sheet that has the other report....."

But there is only one pivot table in the workbook.

Any ideas how to run the pivot refrsh macro?

Many thanks

Greetings I have a workbook with many sheets and i want a macro to protect all the worksheets with certain settings except for one sheet, sheet 3.

I have borrowed some code from this page that I cannot get to work and I would appreciate any help. the code should also have an if statement that unprotects sheet 3 but i don't know how to do that either. I just get syntax errors


	VB:
	
 Workbook_Open() 
     
    Dim wSheet As Worksheet 
     
    For Each wSheet In Worksheets 
         
        wSheet.Protect Password:="secret", DrawingObjects:=True, Contents:=True, Scenarios:= _ 
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ 
        AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _ 
        True _ 
         
        .EnableSelection = xlUnlockedCells 
        .EnableOutlining = True 
         
         
    Next wSheet 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any help is much appreciated,

-The Dude

I followed instruction to protect sheet and allow autofilter but still Excel
2003 sheet will not give me option to autofilter.

I used tools, protection, protect sheet. Next I selected Use AutoFilter and
clicked OK. But the only thing it allows me to do is Advanced Filter.

Is there something else I have to change that may be set wrong by default
somewhere? I know this should work.
--
Brian

I don't know if anyone could help me on this...I was preparing a work on the Excel 2002 and programmed on Woorkbook sheet when open the following:

If ActiveSheet.Protection.AllowFiltering = False Then
ActiveSheet.Protect AllowFiltering:=True
End If

I need to work on a protected sheet and as protected, autofilter is not enable. When I opened on Excel 2000, it doesn't work since it is not compatible.

Does anyone know how could I use a protected sheet and the autofilter enable for using?

Appreciate helps.

Leonardo

Dear all,

I am trying to design a tool which dynamically results in different cells depending on users inputs.

Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells.

Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?

Thanks in advance!

Ares

All,

I need a macro that protects the sheet when the workbook is opened but it will also allow for inserting rows, deleting rows, autofilter use, sorting and selecting unlocked cells. I have searched the forums and have tried different code but can't seem to get all of these items to work. Ususally a couple will but all will not. Let me know if you have any suggestions.

Thanks,
Eamonn


	VB:
	
[SIZE=2][/SIZE] 
[SIZE=2]Sub SHEETPROTECT() 
ActiveSheet.Protect Password:="password" 
End Sub[/SIZE] 
[SIZE=2][/SIZE] 
[SIZE=2][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This code protects the sheet, but still allows all unlocked cells to be accessed and written in.

Can this code be changed to protect the sheet and allow absolutely no changes until it is unprotected again. Bascially all those tick box options you can select when protecting a sheet (allow all users of this worksheet to; I want them all unticked with this code.

For background, the macro running this code will be called "Finalse", and when the user presses it the sheet needs to completely prevent ANY further changes.

Hello,

I have a password protected worksheet, in which I built MACROS to sort some columns from a-z and from z-a.
When trying to built in a MACRO for
- the CLEAR function (filters, if applied) -> X
- deleting a full record (line) -> Y

with including the unprotect and protect function in VBA:

ActiveSheet.Unprotect Password:="pass"

... MARCRO X and Y ...

ActiveSheet.Protect Password:="pass"

I LOSE MY AUTOFILTER OPTION!

What to do in order to USE MACROS in a PROTECTED SHEET without loosing AUTOFILTER option?

Puskas1978

I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:

"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."

The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting

Application.DisplayAlerts = False

into the code, but it does not stop the message.

Does anyone have an idea to assist me with this? Much appreciated.

Hi, guyz/galz

I need a BeforeClose macro to password protect all 13 sheets in my workbook whenever I close the book. I can record one by going to each sheet and protecting it individually, but I know there's much shorter code to do it, and that doesn't require the names of the sheets. Can anyone supply it, please?

Hello, anyone know of a macro to protect certain parts of a sheet. I know about the protect cells checkmark for the cells I don't want protected, but I have 38 sheet and would have to go to each sheet and use that feature. Very time consuming. Is there a way to tell the macro which cells to protect or not protect? Note the protected cells are in different cells and rows depending on the sheet name. If the sheet name is a premium sheet, it is one set of cells, if it is a loss sheet, it is different set.

Thanks

Hi
I have a workbook with some sheets password protected and other sheets not protected.
I have a macro that unprotects the sheets, but it requires the password to be typed multiple times.
I want to put the password into the macro so it doesn't require manual entry of the password lots of times.
Each sheet has the same password, so I would expect to enter it for the first sheet only.

What I have came from a simple macro recorder selecting each sheet in turn:

Sheets("wages").Select
ActiveSheet.Unprotect
Sheets("Comparative").Select
ActiveSheet.Unprotect

Thanks

I have a workbook with 10 sheets, 7 sheets used for Data entry (which I don't
want anyone to be able to change) and 3 for anybody to work with the data on
a day to day basis without them being able to corrupt the original. I know of
the Macro to protect the whole workbook but what I would like is to be able
to protect the 7 Data sheets and leave the 3 working sheets unlocked. Hope
this makes sense to you.
Thx in advance

I am attempting to protect a sheet AND disable the "Select Locked Cells". Only allowing the "Select Unlocked Cells" option.

You can do this manually by going through tool->protection->protect sheet. Then uncheck the "Select Locked Cells" option.

I already have a sheet property setup to with:

.Protect pwrd

However, I cannot find the "Select Unlocked Cells" property.

How do you perform this with VBA code in Excel 2003?

Thanks

I was wondering how you can protect a formula but still allow editing in the cell. Right now I have certain columns locked while allowing others to be edited. I have a formula in one column that needs to be edited if need be but if they make a mistake and hit delete then my formula disappears and it throws the whole sheet off. Is there a way to protect your formula maybe by putting it in a different cell that can be locked and referencing the cell where they can input?

Thanks

Hello all,

I'd like to protect my worksheets, however, it doesn't appear that I can use the default Excel options. On my sheet, there is a drop down to select a reporting period, and then quite a few dynamically updating numbers and graphs. If I protect the sheet, whenever the user selects another date from the drop-down, they are presented with the "read only" error and cannot change the reporting period. Is there a way to protect all of the formulas in the cells, but still allow the cells to update when a user selects a new reporting period? I would post a copy of the sheet here, but there is sensitive company information on there. Instead, I have posted a template that is similar to what I am working with. Thanks in advance.

Hi All,

I'm encoutering this really confusing problem with Excel. I have a macro to protect all sheets and of course another macro to unprotect all sheets. I have attached the macro code to protect all sheets below. Basically I'm using userinterfaceonly:=True to allow my other macros to make changes to the sheets. However, if I run protectallsheets() --> save the workbook --> open the workbook --> run another macro to make changes to the workbook, I get an error "The cell or chart you're trying to change is protected and therefore read-only". Now the funny thing is, if I open the workbook --> run protectallsheets() (NOTICE: i'm still protecting all sheets here) --> run another macro to make changes to the workbook, now IT WORKS!

Any ideas... anyone?


	VB:
	
 protectallsheets() 
     
    Application.ScreenUpdating = False 
     
    Dim password1 As String 
    password1 = InputBox(prompt:="Select Password", Title:="PASSWORD REQUIRED") 
     
    If password1 = "" Then 
        MsgBox "No password selected.  Workbook not protected." 
    Else 
         
        Dim password2 As String 
        password2 = InputBox(prompt:="Caution: If you lose or forget this password, it cannot be recovered. (Remember that
passwords are case sensitive)", Title:="CONFIRM PASSWORD") 
         
        If password2 = "" Then 
            MsgBox "Password not confirmed.  Workbook not protected." 
        Else 
             
            If password1 = password2 Then 
                 
                Dim wsSheet As Worksheet 
                On Error Resume Next 
                For Each wsSheet In Worksheets 
                    wsSheet.Activate 
                    wsSheet.Protect password:=password1, userinterfaceonly:=True, AllowFormattingRows:=True,
AllowFormattingColumns:=True 
                Next wsSheet 
                On Error Goto 0 
                 
                Application.Goto ("home") 
                 
            Else 
                 
                MsgBox "Confirmation password is not identical.  Workbook not protected." 
                 
            End If 
        End If 
    End If 
     
    Application.Goto ("home") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all,

How do I allow people to expand or collapse grouped columns/rows in a protected sheet? I want them to be able to do this without allowing them to modify certain elements/data in the sheet...

I thought I was being clever in recording a macro to protect workbooks with a
password, and then another to unprotect, using ctrl+ a diff letter everytime.

OK, it works, but now to unprotect I'm not even being asked for password!!
This happens whether I use the macro or the "unprotect" feature from the
menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!

Obviously, it sort of defeats the purpose of protecting in the first place,
if anyone can unprotect.

Is this happening, because I'm doing it on the same PC where my macro is
stored?

Help..

Thanks

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Delete Columns".

What I *don't* see on the "Protect Sheet" menu is an option to "Hide Columns"

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Select Locked Cells; Select Unlocked
Cells; Format cells; ".

What I *don't* see on the "Protect Sheet" menu is an option to "Allow all
users of this worksheet to: INSERT COMMENTS into unlocked cells.

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

I have a report sheet with many formulas that I want to protect. I also have 2 radio buttons, from the Forms menue, on the sheet that I wish to keep operational. I have not been able to protect the sheet and have both buttons work. I can only get one button to be unprotected. Any help would be appreciated.

Yhank You. Jim O

I thought I was being clever in recording a macro to protect workbooks with a
password, and then another to unprotect, using ctrl+ a diff letter everytime.

OK, it works, but now to unprotect I'm not even being asked for password!!
This happens whether I use the macro or the "unprotect" feature from the
menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!

Obviously, it sort of defeats the purpose of protecting in the first place,
if anyone can unprotect.

Is this happening, because I'm doing it on the same PC where my macro is
stored?

Help..

Thanks


No luck finding an answer? You could always try Google.