VBA - application defined or object defined error

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

    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:

    Thanks a lot

  • 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

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


    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.


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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!