| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Data filtering functional when protectiong Worksheet
Good morning.
Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
You need to set the enableAutofilter property to true and set protection to
UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
Thanks Tom.
I will use the macro. -----Original Message----- You need to set the enableAutofilter property to true and set protection to UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
Tom;
I entered the macro in the Workbook I wanted to protect. It still locks the auto filter dropdown arrows when the worksheet is protected. The only thing available is advanced filter, which may be too complicated for novice users. What could I be doing wrong? The help menu in VBA shows the same macro lines as you suggested and explained that the filter would be available when the worksheet is protected. So I know I must be missing something. Thanks Phil -----Original Message----- You need to set the enableAutofilter property to true and set protection to UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
There was a subtle typo in the macro. Change it to
Public Sub Auto_Open() Worksheets("sheet1").Activate ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterfaceOnly:=True End Sub Previously the colon was missing in UserInterfaceOnly:=True in the macro. -- Regards, Tom Ogilvy Phil C wrote in message ... Tom; I entered the macro in the Workbook I wanted to protect. It still locks the auto filter dropdown arrows when the worksheet is protected. The only thing available is advanced filter, which may be too complicated for novice users. What could I be doing wrong? The help menu in VBA shows the same macro lines as you suggested and explained that the filter would be available when the worksheet is protected. So I know I must be missing something. Thanks Phil -----Original Message----- You need to set the enableAutofilter property to true and set protection to UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
I changed it but still can't get the dropdown arrows to
function while protected. Is there any specific place this macro should reside. I have it in "This Woekbook" -----Original Message----- There was a subtle typo in the macro. Change it to Public Sub Auto_Open() Worksheets("sheet1").Activate ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterfaceOnly:=True End Sub Previously the colon was missing in UserInterfaceOnly:=True in the macro. -- Regards, Tom Ogilvy Phil C wrote in message ... Tom; I entered the macro in the Workbook I wanted to protect. It still locks the auto filter dropdown arrows when the worksheet is protected. The only thing available is advanced filter, which may be too complicated for novice users. What could I be doing wrong? The help menu in VBA shows the same macro lines as you suggested and explained that the filter would be available when the worksheet is protected. So I know I must be missing something. Thanks Phil -----Original Message----- You need to set the enableAutofilter property to true and set protection to UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keep Data filtering functional when protectiong Worksheet
It should be in a general module.
If you want to use thisworkbook, you should use the workbook)_open event Private Sub Workbook_Open() Worksheets("sheet1").Activate ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterfaceOnly:=True End Sub -- Regards, Tom Ogilvy Phil C wrote in message ... I changed it but still can't get the dropdown arrows to function while protected. Is there any specific place this macro should reside. I have it in "This Woekbook" -----Original Message----- There was a subtle typo in the macro. Change it to Public Sub Auto_Open() Worksheets("sheet1").Activate ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterfaceOnly:=True End Sub Previously the colon was missing in UserInterfaceOnly:=True in the macro. -- Regards, Tom Ogilvy Phil C wrote in message ... Tom; I entered the macro in the Workbook I wanted to protect. It still locks the auto filter dropdown arrows when the worksheet is protected. The only thing available is advanced filter, which may be too complicated for novice users. What could I be doing wrong? The help menu in VBA shows the same macro lines as you suggested and explained that the filter would be available when the worksheet is protected. So I know I must be missing something. Thanks Phil -----Original Message----- You need to set the enableAutofilter property to true and set protection to UserInterfaceOnly:=True. The latter can only be done with a macro. The former needs to be done each time a worksheet is opened. - So I would say you need a special macro Public Sub Auto_Open() worksheets("sheet1").Activate Activesheet.EnableAutofilter = True Activesheet.Protect UserInterfaceOnly = True End Sub as an example. -- Regards, Tom Ogilvy Phil C wrote in message ... Good morning. Is there any way to keep the data filtering function in tact while, at the same time, protecting various selected cells in a spreadsheet? I am using Windows 2000 and Office Excel 2000. Can this be done without the use of a special macro? I tried unchecking various combinations of the Protection, I.E. Objects,Contents,Scenarios, but I can only unlock all the cells or the data filtering becomes unusable also. Thanks Phil . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Which is Better / More Functional / Accurate | Excel Worksheet Functions | |||
| Excel 2007 Nearly Non Functional | Excel Discussion (Misc queries) | |||
| Filtering data and replace on to an new worksheet | Excel Worksheet Functions | |||
| Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
| Filtering data from one worksheet based on another | Excel Discussion (Misc queries) | |||
![Phil C[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
