• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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
0
Shanan212
Asked:
Shanan212
  • 3
  • 2
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Shanan212Author Commented:
Ahh! Didn't think of that! Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now