Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
I have a page set up with groups
any way to use them and protection at the same time I am using 2003 excel. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) "Darrell_Sarrasin via OfficeKB.com" wrote: I have a page set up with groups any way to use them and protection at the same time I am using 2003 excel. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
Hi Dave thanks for the macro. If I run it when the document is open it works
great protects everything I want protected and allows me to use the group feature. If I close the document thou. When i reopen it it is locking the sheet down and not allowing me to use the groups again. Help? Dave Peterson wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) I have a page set up with groups any way to use them and protection at the same time I am using 2003 excel. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
Dave's code is meant to auto run when the workbook opens, not manually run
after the workbook is already open. Did you copy/paste into a general module? Gord Dibben MS Excel MVP On Thu, 25 Sep 2008 20:55:44 GMT, "Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote: Hi Dave thanks for the macro. If I run it when the document is open it works great protects everything I want protected and allows me to use the group feature. If I close the document thou. When i reopen it it is locking the sheet down and not allowing me to use the groups again. Help? Dave Peterson wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) I have a page set up with groups any way to use them and protection at the same time I am using 2003 excel. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
thank you I was doing it in the wrong place. can I use the same code and do
more then one sheet i have two sheets one called attendance record the other called manager Gord Dibben wrote: Dave's code is meant to auto run when the workbook opens, not manually run after the workbook is already open. Did you copy/paste into a general module? Gord Dibben MS Excel MVP Hi Dave thanks for the macro. If I run it when the document is open it works great protects everything I want protected and allows me to use the group [quoted text clipped - 31 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
Since there are only two sheets, you could do:
Option Explicit Sub auto_open() With Worksheets("attendance") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With With Worksheets("manager") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub And it's probably the easiest way if you have different passwords for each sheet. If you have lots of sheets that use the same password, then there are other options. "Darrell_Sarrasin via OfficeKB.com" wrote: thank you I was doing it in the wrong place. can I use the same code and do more then one sheet i have two sheets one called attendance record the other called manager Gord Dibben wrote: Dave's code is meant to auto run when the workbook opens, not manually run after the workbook is already open. Did you copy/paste into a general module? Gord Dibben MS Excel MVP Hi Dave thanks for the macro. If I run it when the document is open it works great protects everything I want protected and allows me to use the group [quoted text clipped - 31 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
thanks those are the only two that have a password thanks!!!
Dave Peterson wrote: Since there are only two sheets, you could do: Option Explicit Sub auto_open() With Worksheets("attendance") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With With Worksheets("manager") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub And it's probably the easiest way if you have different passwords for each sheet. If you have lots of sheets that use the same password, then there are other options. thank you I was doing it in the wrong place. can I use the same code and do more then one sheet i have two sheets one called attendance record the other [quoted text clipped - 16 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
thanks those are the only two that have a password thanks!!!
Dave Peterson wrote: Since there are only two sheets, you could do: Option Explicit Sub auto_open() With Worksheets("attendance") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With With Worksheets("manager") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub And it's probably the easiest way if you have different passwords for each sheet. If you have lots of sheets that use the same password, then there are other options. thank you I was doing it in the wrong place. can I use the same code and do more then one sheet i have two sheets one called attendance record the other [quoted text clipped - 16 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
Just realized this will not let me do formatting now. what is code to turn
formatting on? Darrell_Sarrasin wrote: thanks those are the only two that have a password thanks!!! Since there are only two sheets, you could do: [quoted text clipped - 23 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protection
There's a handful of formatting options you see when you do
Tools|protection|protect sheet. Record a macro when you toggle the ones you want and you'll see the code you need. "Darrell_Sarrasin via OfficeKB.com" wrote: Just realized this will not let me do formatting now. what is code to turn formatting on? Darrell_Sarrasin wrote: thanks those are the only two that have a password thanks!!! Since there are only two sheets, you could do: [quoted text clipped - 23 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protection | Excel Worksheet Functions | |||
WS Protection: Different Levels of Protection on Different Ranges | Excel Discussion (Misc queries) | |||
protection | Excel Discussion (Misc queries) | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) |