Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
I have excel 2003 and have a created a spreadhseet for multipole users to
access. The format of the spreadhseet, data validations and formulas mean that the worksheets need to be protected, which I have done with a password. The protection does allow the use of autofilter. I did this by ticking the appropriate check box in the Protect Sheet dialogue box. The issue is that I have command buttons with macros to do certain tasks such as sort data ranges by date and display various Custom Views. In order for the macros to work each macro VBA code follows a 3 step rule. 1. Unprotect the relevant worksheet(s) 2. Perform task, e.g. display custom view 3. Reprotect the relevant worksheets and allow filtering. The macros all work fine on my machine but if someone else who has excel 2000 clicks the command buttons the macros do not work. The debugging shows that the issue is related to the use of autofilter in protected worksheets. My research so far has led me to believe that the only solution to this is that an upgrade to 2003 is required. An example of the code I have to display a custom view is as follows For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:="putpasswordhere" Next ActiveWorkbook.CustomViews("View1").Show For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Protect Password:="putpasswordhere", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True Next The "AllowFiltering:=True" text only seems to be understood in excel 2003. Is there anhything I can use in my code which will work for 2000. The use of autofilter is a neccessity and when clicking the comman buttons, the macros must work so that after it has ran, the sheets have been reprotected again and the use of autofilter is enabled. Please advise if VBA code can do this or if I need an upgrade to 2003 for other users. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
Hi
You have to write a workbook Open event, which enables autofilter for this sheet every time the workbook is opened. Like this: Private Sub Workbook_Open() Sheets("MySheet1").Unprotect Password:="MyPassword" Sheets("MySheet1").EnableAutoFilter = True Sheets("MySheet1").Protect Password:="MyPassword", UserInterfaceOnly:=True End Sub To avoid that user reads you password from VBA code, you may protect your VBA project with password too. Arvi Laanemets "dazzag82" wrote in message ... I have excel 2003 and have a created a spreadhseet for multipole users to access. The format of the spreadhseet, data validations and formulas mean that the worksheets need to be protected, which I have done with a password. The protection does allow the use of autofilter. I did this by ticking the appropriate check box in the Protect Sheet dialogue box. The issue is that I have command buttons with macros to do certain tasks such as sort data ranges by date and display various Custom Views. In order for the macros to work each macro VBA code follows a 3 step rule. 1. Unprotect the relevant worksheet(s) 2. Perform task, e.g. display custom view 3. Reprotect the relevant worksheets and allow filtering. The macros all work fine on my machine but if someone else who has excel 2000 clicks the command buttons the macros do not work. The debugging shows that the issue is related to the use of autofilter in protected worksheets. My research so far has led me to believe that the only solution to this is that an upgrade to 2003 is required. An example of the code I have to display a custom view is as follows For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:="putpasswordhere" Next ActiveWorkbook.CustomViews("View1").Show For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Protect Password:="putpasswordhere", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True Next The "AllowFiltering:=True" text only seems to be understood in excel 2003. Is there anhything I can use in my code which will work for 2000. The use of autofilter is a neccessity and when clicking the comman buttons, the macros must work so that after it has ran, the sheets have been reprotected again and the use of autofilter is enabled. Please advise if VBA code can do this or if I need an upgrade to 2003 for other users. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
Thanks for that thought it did not seem to work. Your code enables the
autofilter when the file is opnened. Lets say a user has the file open, makes changes to the data and then hits one of the command buttons to do a date sort, the macro will unprotect the sheet, do the date sort and then reprotect it again. I need to have the autofilter available at all times, especially if data has been modified. Can you help? "Arvi Laanemets" wrote: Hi You have to write a workbook Open event, which enables autofilter for this sheet every time the workbook is opened. Like this: Private Sub Workbook_Open() Sheets("MySheet1").Unprotect Password:="MyPassword" Sheets("MySheet1").EnableAutoFilter = True Sheets("MySheet1").Protect Password:="MyPassword", UserInterfaceOnly:=True End Sub To avoid that user reads you password from VBA code, you may protect your VBA project with password too. Arvi Laanemets "dazzag82" wrote in message ... I have excel 2003 and have a created a spreadhseet for multipole users to access. The format of the spreadhseet, data validations and formulas mean that the worksheets need to be protected, which I have done with a password. The protection does allow the use of autofilter. I did this by ticking the appropriate check box in the Protect Sheet dialogue box. The issue is that I have command buttons with macros to do certain tasks such as sort data ranges by date and display various Custom Views. In order for the macros to work each macro VBA code follows a 3 step rule. 1. Unprotect the relevant worksheet(s) 2. Perform task, e.g. display custom view 3. Reprotect the relevant worksheets and allow filtering. The macros all work fine on my machine but if someone else who has excel 2000 clicks the command buttons the macros do not work. The debugging shows that the issue is related to the use of autofilter in protected worksheets. My research so far has led me to believe that the only solution to this is that an upgrade to 2003 is required. An example of the code I have to display a custom view is as follows For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:="putpasswordhere" Next ActiveWorkbook.CustomViews("View1").Show For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Protect Password:="putpasswordhere", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True Next The "AllowFiltering:=True" text only seems to be understood in excel 2003. Is there anhything I can use in my code which will work for 2000. The use of autofilter is a neccessity and when clicking the comman buttons, the macros must work so that after it has ran, the sheets have been reprotected again and the use of autofilter is enabled. Please advise if VBA code can do this or if I need an upgrade to 2003 for other users. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
See
http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "dazzag82" wrote in message ... Thanks for that thought it did not seem to work. Your code enables the autofilter when the file is opnened. Lets say a user has the file open, makes changes to the data and then hits one of the command buttons to do a date sort, the macro will unprotect the sheet, do the date sort and then reprotect it again. I need to have the autofilter available at all times, especially if data has been modified. Can you help? "Arvi Laanemets" wrote: Hi You have to write a workbook Open event, which enables autofilter for this sheet every time the workbook is opened. Like this: Private Sub Workbook_Open() Sheets("MySheet1").Unprotect Password:="MyPassword" Sheets("MySheet1").EnableAutoFilter = True Sheets("MySheet1").Protect Password:="MyPassword", UserInterfaceOnly:=True End Sub To avoid that user reads you password from VBA code, you may protect your VBA project with password too. Arvi Laanemets "dazzag82" wrote in message ... I have excel 2003 and have a created a spreadhseet for multipole users to access. The format of the spreadhseet, data validations and formulas mean that the worksheets need to be protected, which I have done with a password. The protection does allow the use of autofilter. I did this by ticking the appropriate check box in the Protect Sheet dialogue box. The issue is that I have command buttons with macros to do certain tasks such as sort data ranges by date and display various Custom Views. In order for the macros to work each macro VBA code follows a 3 step rule. 1. Unprotect the relevant worksheet(s) 2. Perform task, e.g. display custom view 3. Reprotect the relevant worksheets and allow filtering. The macros all work fine on my machine but if someone else who has excel 2000 clicks the command buttons the macros do not work. The debugging shows that the issue is related to the use of autofilter in protected worksheets. My research so far has led me to believe that the only solution to this is that an upgrade to 2003 is required. An example of the code I have to display a custom view is as follows For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:="putpasswordhere" Next ActiveWorkbook.CustomViews("View1").Show For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Protect Password:="putpasswordhere", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True Next The "AllowFiltering:=True" text only seems to be understood in excel 2003. Is there anhything I can use in my code which will work for 2000. The use of autofilter is a neccessity and when clicking the comman buttons, the macros must work so that after it has ran, the sheets have been reprotected again and the use of autofilter is enabled. Please advise if VBA code can do this or if I need an upgrade to 2003 for other users. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
Hi
The autofilter remains allowed until end of session. 1. User opens the workbook - the Open event is fired, it removes the password, enables autofilter, and protects the sheet again; 2. The user does whatever he/she does - autofilter feature for protected sheet works normally through entire session; 3. The user closes workbook - autofilter settings aren't saved along with it, so next for next session they must be reset again. Arvi Laanemets "dazzag82" wrote in message ... Thanks for that thought it did not seem to work. Your code enables the autofilter when the file is opnened. Lets say a user has the file open, makes changes to the data and then hits one of the command buttons to do a date sort, the macro will unprotect the sheet, do the date sort and then reprotect it again. I need to have the autofilter available at all times, especially if data has been modified. Can you help? "Arvi Laanemets" wrote: Hi You have to write a workbook Open event, which enables autofilter for this sheet every time the workbook is opened. Like this: Private Sub Workbook_Open() Sheets("MySheet1").Unprotect Password:="MyPassword" Sheets("MySheet1").EnableAutoFilter = True Sheets("MySheet1").Protect Password:="MyPassword", UserInterfaceOnly:=True End Sub To avoid that user reads you password from VBA code, you may protect your VBA project with password too. Arvi Laanemets "dazzag82" wrote in message ... I have excel 2003 and have a created a spreadhseet for multipole users to access. The format of the spreadhseet, data validations and formulas mean that the worksheets need to be protected, which I have done with a password. The protection does allow the use of autofilter. I did this by ticking the appropriate check box in the Protect Sheet dialogue box. The issue is that I have command buttons with macros to do certain tasks such as sort data ranges by date and display various Custom Views. In order for the macros to work each macro VBA code follows a 3 step rule. 1. Unprotect the relevant worksheet(s) 2. Perform task, e.g. display custom view 3. Reprotect the relevant worksheets and allow filtering. The macros all work fine on my machine but if someone else who has excel 2000 clicks the command buttons the macros do not work. The debugging shows that the issue is related to the use of autofilter in protected worksheets. My research so far has led me to believe that the only solution to this is that an upgrade to 2003 is required. An example of the code I have to display a custom view is as follows For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:="putpasswordhere" Next ActiveWorkbook.CustomViews("View1").Show For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Protect Password:="putpasswordhere", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True Next The "AllowFiltering:=True" text only seems to be understood in excel 2003. Is there anhything I can use in my code which will work for 2000. The use of autofilter is a neccessity and when clicking the comman buttons, the macros must work so that after it has ran, the sheets have been reprotected again and the use of autofilter is enabled. Please advise if VBA code can do this or if I need an upgrade to 2003 for other users. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
NB! You have to create an Open event for workbook, not some procedure in
standard module! When you open VBA editor, in VBA Project window locate YourProjectMicrosoft Excel ObjectsThisWorkbook, right-click on it, and select View Code. At top of code window are 2 dropdowns - from left one select Workbook, and a dummy Open event is created. Edit it. Arvi Laanemets |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable autofilter in protected worksheets in Excel 2000
Thanks Arvi
I had to modify some of my other existing macros and I got it to work perfectly. You have saved me thousands of dollars in people not needing an upgrade. Thank You so much! "Arvi Laanemets" wrote: NB! You have to create an Open event for workbook, not some procedure in standard module! When you open VBA editor, in VBA Project window locate YourProjectMicrosoft Excel ObjectsThisWorkbook, right-click on it, and select View Code. At top of code window are 2 dropdowns - from left one select Workbook, and a dummy Open event is created. Edit it. Arvi Laanemets . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using autofilter in a shared protected workbook excel 2000? | Excel Discussion (Misc queries) | |||
problems w solution 4 enable AutoFilter for a protected worksheet | Excel Programming | |||
How do I enable autofilter on a protected shared workbook? | Excel Discussion (Misc queries) | |||
enable autofilter in a protected worksheet in Excel 97 | Excel Worksheet Functions | |||
Enable filter to work in a protected sheet from Excel 2003 to 2000 | Excel Programming |