Protection with UserInterfaceOnly:= TRUE

georgec

New Member
Joined
Dec 15, 2002
Messages
45
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
Instead of protect and unprotect routines use the folloing code in teh open workbooks event

Code:
Sheet1.Protect userinterfaceOnly:=True

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

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top