Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hello:
I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hi murkaboris
Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hello Ron:
Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hi Monika
See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hello Ron:
Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hi Monika
If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Hi Ron:
Still missing something, sorry for the trouble. Now I'm getting the "Compile error: - invalid or unqualified reference". Here is my code: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets Next Sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With End Sub Please help. Thank you. Monika "Ron de Bruin" wrote: Hi Monika If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Private Sub Workbook_Open()
'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets with sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With Next Sh End Sub murkaboris wrote: Hi Ron: Still missing something, sorry for the trouble. Now I'm getting the "Compile error: - invalid or unqualified reference". Here is my code: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets Next Sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With End Sub Please help. Thank you. Monika "Ron de Bruin" wrote: Hi Monika If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
You have somehow mangles the code.
This revision seems to work.......assuming you have data in A1 Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each sh In ActiveWorkbook.Worksheets With sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With Next sh End Sub Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 12:37:02 -0700, murkaboris wrote: Hi Ron: Still missing something, sorry for the trouble. Now I'm getting the "Compile error: - invalid or unqualified reference". Here is my code: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets Next Sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With End Sub Please help. Thank you. Monika "Ron de Bruin" wrote: Hi Monika If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
Thank you Dave, worked like a charm!
I have to remember to put the Next sh at the end instead of on top... Thanks again! Monika "Dave Peterson" wrote: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets with sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With Next Sh End Sub murkaboris wrote: Hi Ron: Still missing something, sorry for the trouble. Now I'm getting the "Compile error: - invalid or unqualified reference". Here is my code: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets Next Sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With End Sub Please help. Thank you. Monika "Ron de Bruin" wrote: Hi Monika If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilters not working when sheet is protected
I should have asked about this before...
Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets with sh .unprotect password:="ib" "<-- don't you need this, too???? If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With Next Sh End Sub murkaboris wrote: Thank you Dave, worked like a charm! I have to remember to put the Next sh at the end instead of on top... Thanks again! Monika "Dave Peterson" wrote: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets with sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With Next Sh End Sub murkaboris wrote: Hi Ron: Still missing something, sorry for the trouble. Now I'm getting the "Compile error: - invalid or unqualified reference". Here is my code: Private Sub Workbook_Open() 'check for filter, turn on if none exists For Each Sh In ActiveWorkbook.Worksheets Next Sh If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="ib", _ Contents:=True, UserInterfaceOnly:=True End With End Sub Please help. Thank you. Monika "Ron de Bruin" wrote: Hi Monika If you want to do it in all sheets For Each Sh In ActiveWorkbook.Worksheets Next Sh Or for a few sheets For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")) Next Sh -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Thank you for the reference. Just one more quick question when you are listing multiple worksheets in a workbook for the code do you know the proper way to include multiple ones? With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the right way. I'd like the code to run on all of the worksheets in my workbook. "CT" and "MR" are two separate worksheets.... Thank you. Monika "Ron de Bruin" wrote: Hi Monika See Debra's site http://www.contextures.com/xlautofilter03.html#Protect -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello Ron: Unfortunately I'm on the older version of Excel that doesn't have this option. Please advise. Thank you. Monika "Ron de Bruin" wrote: Hi murkaboris Unprotect the worksheet Turn Autofilter on Protect the worksheet and in the protect dialog check "Use AutoFilter" If you have a very old version of Excel and not see this option in the protect sheet dialog post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "murkaboris" wrote in message ... Hello: I was wondering if there is a way to make the autofilters work while the sheet is protected. Specifically I have created List of Values for certain column and want to limit the users choosing from them and not be able to change any other fields that are already typed in thus the protection. The List of values seems to work fine with the protection but the Autofilters are not working. Is there a way to make them function while keeping the worksheets protected? Please help. Thank you. Monika -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro stops working when sheet is protected | Excel Discussion (Misc queries) | |||
UDF is not working on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
Filters not working on Protected sheet | Excel Discussion (Misc queries) | |||
Hyperlinks not working after sheet is protected.... | Excel Discussion (Misc queries) | |||
Find Function not working on protected sheet | Excel Worksheet Functions |