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).
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
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
LVL 19
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
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.
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.
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?
ActiveSheet.AutoFilterMode = False
Works on removing the autofilter, but user cannot enable autofilter as its locked :/
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(pass
ActiveSheet.ShowAllData
ActiveSheet.Protect(passwo
End Sub
Sub Macro1()
'
' Macro1 Macro
'
ActiveSheet.Unprotect(pass
ActiveSheet.ShowAllData
ActiveSheet.Protect(passwo
End Sub
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 trialIt'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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.