Protecting sheet

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:51 PM GMT

Question

QUESTION: Good day Bob, I want to protect a worksheet but I would like to let some features available to the user. Seeking info on the net, I came across some code which I modified to suit my needs. I get this message when compiling: "Method or data member not found" What Am I doing wrong?

Private Sub Workbook_Open()

   With Employee   'Sheet Name, not the TAB name.
        .Protect Password:="My_Secret", UserInterfaceOnly:=True
       .EnableOutlining = True
       .AllowFiltering = True
       .AllowFormattingColumns = True
       .AllowFormattingRows = False
       .AllowInsertingColumns = False
       .AllowInsertingRows = True
       .AllowInsertingHyperlinks = False
       .AllowDeletingColumns = False
       .AllowDeletingRows = True
       .AllowSorting = True
       .AllowFiltering = True
       .AllowUsingPivotTables = True
   End With

End Sub

ANSWER: Most likely you found an answer for2007 and you're using it in Excel 2003? Or 2010 in 2007? You didn't say whcih line is giving the error.

---------- FOLLOW-UP ----------

QUESTION: I'm using2003.

The error is at line .Allowfiltering = True

ANSWER: You have things like .AllowFiltering = True as if "AllowFiltering" is a property or method of the worksheet, and it's not.

You would need something like:

.Protect Password:="My_Secret",UserInterfaceOnly:=True,Scenarios:= _

       False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
       AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
       :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
       AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
       AllowUsingPivotTables:=True

---------- FOLLOW-UP ----------

QUESTION: I do not know what I'm doing wrong but the code given is not working. I have set the AllowInsertingRows to TRUE (meaning I can insert) but it will not (I have closed the file and reopened it to ensure the macro was properly executed)

I even CANNOT select any cells. Also I would like to be able to use the Outlining but I do not know where to put the code: .EnableOutlining = True

Can you help? Regards.

Private Sub Workbook_Open()

   With Employee   'Sheet Name, not the TAB name.
        .Protect Password:="My_Secret", UserInterfaceOnly:=True, Scenarios:= _
       False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
       AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows _
       :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=False, _
       AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
       AllowUsingPivotTables:=True
   End With

End Sub

Answer

You would also need to include contents:=False if you want to allow cells to be changed, otherwise every cell not marked as unlocked would be unable to change. Don't know why you can't SELECT cells, tho - something else is going on. You would put .EnableOutlining = True just before the End With -- that is, ... ... .EnableOutlining=True End With

Advertisement

©2021 eLuminary LLC. All rights reserved.