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
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
Last Comment
Shanan212
SOLUTION
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.
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
error is: Showalldata method of worksheet failed
Any ideas?
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
error is: Showalldata method of worksheet failed
Any ideas?
ASKER
ActiveSheet.AutoFilterMode = False
Works on removing the autofilter, but user cannot enable autofilter as its locked :/
ASKER CERTIFIED SOLUTION
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.
ASKER
Ahh! Didn't think of that! Thanks!
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.