VBA - application defined or object defined error

jijy · Jul 10th 2006

I am badly in need of help (rolling out the file in few hours)

I have a spreadsheet (excel 2003) with several pages with formulas and I want to protect them while allowing users to use filters, allow cell comments, increase column width etc.

I recorded a macro and modified it a bit to input a password and to use a shortcut because I don't want users to unprotect them from the menu.

When run the short cut to protect the sheet, I get the error
'Run time error 1004'
application defined or object defined error.

When i click on debug, the following gets highlighted in yellow.

Code

ActiveSheet.Protect EnableAutoFilter:=True, AllowFormattingColumns:=True, EnableEditObjects:= _ 
True, AllowUsingPivotTables:=True, contents:=True, DrawingObjects:=False, AllowUsingPivotTables:=True 
'.AllowFiltering = True

I am very new vba and I know i messed up something. Please any one of you gurus help me. I have no clue what is wrong.

Following is the code:

Code

Sub Protect() 
' 
' Protect Macro 
' Macro recorded 09/07/2006 by Jijy 
' Keyboard Shortcut: Ctrl+Shift+P 
' 
Application.ScreenUpdating = False 
For i = 1 To Sheets.Count 
Sheets(i).Protect "Protect Sheets" 
ActiveSheet.Protect EnableAutoFilter:=True, AllowFormattingColumns:=True, EnableEditObjects:= _ 
True, AllowUsingPivotTables:=True, contents:=True, DrawingObjects:=False, AllowUsingPivotTables:=True 
'.AllowFiltering = True 
'Sheets(i).Scenarios = True 




' ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _ 
True, AllowFormattingColumns:=True, AllowFiltering:=True, _ 
AllowUsingPivotTables:=True 




'Next i 
Application.ScreenUpdating = True 
'Private Sub Workbook_Open() 
With Worksheets("PPK & DRP") 
.EnableOutlining = True 
.Protect contents:=True, userInterfaceOnly:=True 
End With 


Next i 
Application.ScreenUpdating = True 
End Sub

Display More

Code

Sub unprotect() 
' 
' unprotect Macro 
' Macro recorded 09/07/2006 by Jijy 
' 
' Keyboard Shortcut: Ctrl+Shift+U 
' 
Application.ScreenUpdating = False 
For i = 1 To Sheets.Count 
Sheets(i).UnProtect "Protect Sheets" 


Next i 
Application.ScreenUpdating = True 
End Sub

Display More

Thanks a lot

StefanG · Jul 10th 2006

Re: VBA - application defined or object defined error

Hi jijy,
I changed EnableAutoFilter to AllowFiltering. I cant seem to find a reference to EnableAutoFilter and EnableEditObjects which seemed to cause a problem. There was twice reference to AllowUsingPivotTables. I changed

Code

Sheets(i).Protect "Protect Sheets" 
        ActiveSheet.Protect '....

to

Code

Sheets(I).Protect "Protect Sheets"'....

So something likes this seems to work just fine ... have a look at Excel VBA Help under "Protect Method" for the different protection options and, of course, adjust as needed.

Stefan

Code

Option Explicit
Sub Protect()
Dim I
    Application.ScreenUpdating = False
    For I = 1 To Sheets.Count
        Sheets(I).Protect "Protect Sheets", _
            AllowFiltering:=True, _
            AllowFormattingColumns:=True, _
            AllowUsingPivotTables:=True, _
            contents:=True, _
            DrawingObjects:=False


        With Worksheets("PPK & DRP")
            .EnableOutlining = True
            .Protect contents:=True, userInterfaceOnly:=True
        End With


    Next I
    Application.ScreenUpdating = True
End Sub


Sub Unprotect()
Dim I
    Application.ScreenUpdating = False
    For I = 1 To Sheets.Count
        Sheets(I).Unprotect "Protect Sheets"
    Next I
    Application.ScreenUpdating = True
End Sub

Display More

jijy · Jul 10th 2006

Re: VBA - application defined or object defined error

Thanks a lot Stefan.

It works and I was getting concerned as I was running short of time. Your timely help is very much appreciated.

Jijy

VBA - application defined or object defined error

Participate now!

Share