Protection with UserInterfaceOnly:= TRUE

[georgec]

ActiveSheet.Protect UserInterfaceOnly:=True allows me to protect sheets whilst still being able to write to cells from code.

But I cannot use code to put AutoFiltering on or off while sheet is protected for UserInterfaceOnly. How do I do this.

I have written a filter procedure that sets row hights to zero for rows that don't match the criteria but I would like to simply use Excels AutoFilter methods from the code while protection is on. I don't like turning protection on and off in code.

Thanks

 

[Derek]

Hi georgec

You could try using this macro in a module. O

Sub protect1()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True
End Sub

Hope this helps
regards
Derek

 

[salmagundi]

Hi Derek,

I'm having a similar problem to georgec (well i think i do!). I have a checkbox (from control menu) and the cell its linked to i need to lock so it can't be accidentaly altered, but when i lock the sheet i get a message saying the cell is protected. I've tried using the code you wrote but it still doesn't seem to work:

Sub protect1()
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True
End Sub

Could you help at all please?

(sorry if i'm highjacking your request georgec)

Many thanks,
very confused person

 

[Bill Rockenbach]

Instead of protect and unprotect routines use the folloing code in teh open workbooks event

Sheet1.Protect userinterfaceOnly:=True

It protects against users, but aloows procedures to cahnge items on teh sheet

 

[Bill Rockenbach]

salmagundi,
I believe the userinterfaceOnly:=True has to be set up when you open the workbook