Autofilter and protection
 

Hello,

I have a list in Excel 2000, the values in the list are derived from
formulas in the cells of the list. I use an autofilter to sort out the
blank cells in the list. The problem: I would like to protect the list to
keep the formulas intact, but when I protect the list the autofilter won't
function. I've tried unlocking the cell with the filter button, no good. I
tried each one of the switches on the protect (password) window, no good.

Is it possible to protect the list and keep the filter functional?

Thanks!

RJH

Autofilter and protection
 

Hi RJH,

Does "UserInterfaceOnly = True" make sense?


---
Message posted from http://www.ExcelForum.com/

Autofilter and protection
 

Hi,

You can do this by checking the "Use AutoFilters" option
when protecting the sheet. Go to Tools-Protection-
Protect Sheet on the window that appears select the "USe
AutoFilter" option. The user can then still use the
AutoFilter but cannot edit the cells or formulas.

HTH

Gabhan Berry
AI Prediction and Forecasting in Excel
http://www.pececillo.com


-----Original Message-----
Hello,

I have a list in Excel 2000, the values in the list are
derived from
formulas in the cells of the list. I use an autofilter
to sort out the
blank cells in the list. The problem: I would like to
protect the list to
keep the formulas intact, but when I protect the list
the autofilter won't
function. I've tried unlocking the cell with the filter
button, no good. I
tried each one of the switches on the protect (password)
window, no good.

Is it possible to protect the list and keep the filter
functional?

Thanks!

RJH


.

Autofilter and protection
 

If you protect the worksheet in code, you can do it:

Option Explicit
Sub auto_open()
With Worksheets("Sheet1")
.Protect Password:="hi", _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

(The worksheet has to have the dropdown arrows already applied.)

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

But there are some things that macros can't do even with
userinterfaceonly:=true. You'd have to unprotect the sheet, do the work and
reprotect the sheet.

(you could use the workbook_open even under ThisWorkbook, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

That said, worksheet protection is very weak. There's code posted here every
day/week that would unprotect the worksheet.

RJH wrote:

Hello,

I have a list in Excel 2000, the values in the list are derived from
formulas in the cells of the list. I use an autofilter to sort out the
blank cells in the list. The problem: I would like to protect the list to
keep the formulas intact, but when I protect the list the autofilter won't
function. I've tried unlocking the cell with the filter button, no good. I
tried each one of the switches on the protect (password) window, no good.

Is it possible to protect the list and keep the filter functional?

Thanks!

RJH

--

Dave Peterson

Autofilter and protection
 

The version of Excel I'm using (Excel 2000, SP-3) doesn't have those options
in the Protection window.
RJH

wrote in message
...
Hi,

You can do this by checking the "Use AutoFilters" option
when protecting the sheet. Go to Tools-Protection-
Protect Sheet on the window that appears select the "USe
AutoFilter" option. The user can then still use the
AutoFilter but cannot edit the cells or formulas.

HTH

Gabhan Berry
AI Prediction and Forecasting in Excel
http://www.pececillo.com


-----Original Message-----
Hello,

I have a list in Excel 2000, the values in the list are
derived from
formulas in the cells of the list. I use an autofilter
to sort out the
blank cells in the list. The problem: I would like to
protect the list to
keep the formulas intact, but when I protect the list
the autofilter won't
function. I've tried unlocking the cell with the filter
button, no good. I
tried each one of the switches on the protect (password)
window, no good.

Is it possible to protect the list and keep the filter
functional?

Thanks!

RJH


.

Autofilter and protection
 

Thanks for your post. I'm very new to macros. I'll try your suggestion and
check out the site you suggested.
Thanks!

RJH


"Dave Peterson" wrote in message
...

If you protect the worksheet in code, you can do it:

Option Explicit
Sub auto_open()
With Worksheets("Sheet1")
.Protect Password:="hi", _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

(The worksheet has to have the dropdown arrows already applied.)

It needs to be reset each time you open the workbook. (excel doesn't
remember
it after closing the workbook.)

But there are some things that macros can't do even with
userinterfaceonly:=true. You'd have to unprotect the sheet, do the work
and
reprotect the sheet.

(you could use the workbook_open even under ThisWorkbook, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

That said, worksheet protection is very weak. There's code posted here
every
day/week that would unprotect the worksheet.

RJH wrote:

Hello,

I have a list in Excel 2000, the values in the list are derived from
formulas in the cells of the list. I use an autofilter to sort out the
blank cells in the list. The problem: I would like to protect the list
to
keep the formulas intact, but when I protect the list the autofilter
won't
function. I've tried unlocking the cell with the filter button, no
good. I
tried each one of the switches on the protect (password) window, no
good.

Is it possible to protect the list and keep the filter functional?

Thanks!

RJH

--

Dave Peterson