Allow user to run a macro in protected mode

I want to let other users of my spreadsheet to run a macro in protected mode.

The macro runs the filter for a big spreadsheet and I want to keep the
protection on but let users run the simple macro that hides the blank lines
with auto-filters.

Users get Run-time error 1004 when they click on my macro icon

The cells you are manipulating have to be unprotected during the manipulation.

You can do this by putting this piece of code at the beginning of your macro.

Dim Code
Code = "password"
Worksheets("sheetname).Unprotect (PrivCode)

Then at the end of your code put this

Worksheets("sheetname).Protect (PrivCode)

You will have to password protect your VBA editor for that Workbook but this will allow the macro to change the sheet as it needs to.

Good evening Jean Cloutier

You should be able to run a macro in a protected sheet. The error will be caused by attempting to filter items in a protected sheet.

If you are using Excel 2002 or higher, you can set the filter to run in a protected sheet from the protection dialog.

If you are using XL2000 or prior, then look here to see how to handle filters in a protected sheet:

http://www.contextures.com/xlautofilter03.html#Protect

HTH

DominicB