Avatar of Shanan212
Shanan212
Flag for Canada asked on

Locking the cells and able to filter

Hi,

I have a table with some formulas (which contribute to the table) on the left side.

I have some users who constantly delete the rows; hence the formulas. They forget to pull down the formula to compensate. This creates problems with later reports.

I do know how to 'lock' the cells where the formulas are and protect the worksheet. However, this prevents the 'filtering' functionality, which is also important.

I've tried enabling filtering while protecting the sheet but it seems (Office 2007) you cannot filter even if you lock a single cell in a sheet.

Is there anyway to protect the formulas from deletion? (the formulas has to be visible since the values they output are part of table)

Any VBA ways exists?

Thank you for your help!
Shanan
Microsoft Excel

Avatar of undefined
Last Comment
Shanan212

8/22/2022 - Mon
SOLUTION
regmigrant

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Arno Koster

you can select a number of options that you want to disable when you protect the worksheet.
If you allow the user to select protected cells and enable autofilter, the users can still perform filtering functionality.
When you turn on worksheet protection, you will get a list of available options. The autofilter option is not visible in the default view, but if you scroll down the list you will find it.
Shanan212

ASKER
Reg,

That is helpful as I've tried Akoster's suggestion before but its not helping. Turning autofilter on before protecting the sheet (including selecting 'autofilter option') works.

However, now the filter options in the ribbon is disabled. (although I can filter)

The reason I am saying this is because the user is complaining about how to 'remove' all filters.

I know there is few ways such as selecting the filtered column and removing it...but user is asking for a button.

So I designed a button, assigned a macro and put in this function

Sub Macro1()
'
' Macro1 Macro
'

ActiveSheet.ShowAllData
End Sub

Open in new window


error is: Showalldata method of worksheet failed

Any ideas?
Shanan212

ASKER
ActiveSheet.AutoFilterMode = False

Open in new window


Works on removing the autofilter, but user cannot enable autofilter as its locked :/
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
regmigrant

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shanan212

ASKER
Ahh! Didn't think of that! Thanks!