Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
iwgunter
 
Posts: n/a
Default Auto-Filter Not Working With Protection Turned On


I am using columns A-L with content. I want certain cols to be protected
as they bring inthe dynamic content.

Firstly, I selected the whole worksheet [ctrl+a], right clicked and
took the tick out of Locked, I then highlighted the whole of cols G, H,
I & K by clicking on the column letter and checked the Locked box. I
then highlighted the whole of row 1 as this is the column headers and I
have auto-filter running and unchecked the Locked box. Then I applied
the protection so the cells can't be changed.

But now the auto-filter won't work. Is this a known problem with
Excel?

I'm using Excel 2000 [9.0.2720]

Ta
Ian


--
iwgunter
------------------------------------------------------------------------
iwgunter's Profile: http://www.excelforum.com/member.php...o&userid=15441
View this thread: http://www.excelforum.com/showthread...hreadid=513526

  #2   Report Post  
Posted to microsoft.public.excel.misc
Roger Govier
 
Posts: n/a
Default Auto-Filter Not Working With Protection Turned On

Hi

You will need to put protection on, with the userinterface still set to
True.
Enter some code like the following into a standard module of your
Workbook to invoke the Protection.
Change the "mypassword" to whatever you want.

Sub Protectsheet()
With ActiveSheet
.EnableAutoFilter = True
.Protect Password:="mypassword", DrawingObjects:=True, _
contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub

Sub UnProtectSheet()
With ActiveSheet
.Unprotect Password:="mypassword"
End With
End Sub

You can copy the code I posted and paste it into your Visual Basic
Editor
(VBE) in a Standard Module located in your project (workbook). Shortcut
keys would be ..

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select Workbook on left
If no modules exist:
Insert | Module
Paste code in Module
If modules exist:
Double click desired module
Paste code in Module
Add further code if requiredClick on the Excel icon at top left of the
VB Editor to return to the Worksheet.

I usually add shortcut keys to the macros for my own use.
ToolsMacroMacrosSelect ProtectsheetOptions and enter a letter code
in the box
Repeat for the Unprotect macro.

Then you can quickly switch protection on and off for yourself with
these shortcuts.



--
Regards

Roger Govier


"iwgunter" wrote
in message ...

I am using columns A-L with content. I want certain cols to be
protected
as they bring inthe dynamic content.

Firstly, I selected the whole worksheet [ctrl+a], right clicked and
took the tick out of Locked, I then highlighted the whole of cols G,
H,
I & K by clicking on the column letter and checked the Locked box. I
then highlighted the whole of row 1 as this is the column headers and
I
have auto-filter running and unchecked the Locked box. Then I applied
the protection so the cells can't be changed.

But now the auto-filter won't work. Is this a known problem with
Excel?

I'm using Excel 2000 [9.0.2720]

Ta
Ian


--
iwgunter
------------------------------------------------------------------------
iwgunter's Profile:
http://www.excelforum.com/member.php...o&userid=15441
View this thread:
http://www.excelforum.com/showthread...hreadid=513526



  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Auto-Filter Not Working With Protection Turned On

If you already have the outline/subtotals applied, you can protect the worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

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

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

iwgunter wrote:

I am using columns A-L with content. I want certain cols to be protected
as they bring inthe dynamic content.

Firstly, I selected the whole worksheet [ctrl+a], right clicked and
took the tick out of Locked, I then highlighted the whole of cols G, H,
I & K by clicking on the column letter and checked the Locked box. I
then highlighted the whole of row 1 as this is the column headers and I
have auto-filter running and unchecked the Locked box. Then I applied
the protection so the cells can't be changed.

But now the auto-filter won't work. Is this a known problem with
Excel?

I'm using Excel 2000 [9.0.2720]

Ta
Ian

--
iwgunter
------------------------------------------------------------------------
iwgunter's Profile: http://www.excelforum.com/member.php...o&userid=15441
View this thread: http://www.excelforum.com/showthread...hreadid=513526


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code error with Protection turned on - help please Fred Excel Discussion (Misc queries) 1 March 17th 06 05:06 PM
Column character width on auto filter Floyd III Excel Discussion (Misc queries) 2 January 7th 05 02:59 AM
auto filter question Juco Excel Worksheet Functions 1 November 28th 04 03:51 PM
The Auto Filter button lost the column specified option. D Excel Worksheet Functions 1 November 5th 04 12:47 AM
Why can't my macro use Auto Filter when I told the Sheet Protecti. KC Rippstein Excel Worksheet Functions 1 October 28th 04 06:13 PM


All times are GMT +1. The time now is 11:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"