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
LVL 13
Shanan212Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
If you want to enable filtering, turn autofilter on before you protect the sheet and then select the 'use autofilter option' in the protect dialog. If you dont have autofilter on first you cant turn it on after

0
Arno KosterCommented:
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.
0
Shanan212Author Commented:
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?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Shanan212Author Commented:
ActiveSheet.AutoFilterMode = False

Open in new window


Works on removing the autofilter, but user cannot enable autofilter as its locked :/
0
regmigrantCommented:
you need to unprotect the sheet before you try and ShowAllData, if you are password protecting the password will be visible in the Macro so beware!

Sub Macro1()
'
' Macro1 Macro
'
ActiveSheet.Unprotect(password)

ActiveSheet.ShowAllData

ActiveSheet.Protect(password)
End Sub


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shanan212Author Commented:
Ahh! Didn't think of that! Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.