| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
protect the worksheet but still can do the "group and outline" dat
I need to set a password to protect the worksheet. In the meanwhile, I still
should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
protect the worksheet but still can do the "group and outline" dat
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.) lalann wrote: I need to set a password to protect the worksheet. In the meanwhile, I still should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
protect the worksheet but still can do the "group and outline"
Hi Dave,
Many thanks for your reply. However, I use your code, but it still doesn't work. I tried another way, and it works. Private Sub Workbook_Open() Worksheets("Sheet1").Protect Password:="hi", userinterfaceonly:=True Worksheets("Sheet1").EnableOutlining = True End Sub I am not so familiar with code, so I don't know if these two codes mean the same thing. Thanks anyway. "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.) lalann wrote: I need to set a password to protect the worksheet. In the meanwhile, I still should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
protect the worksheet but still can do the "group and outline"
They do the same thing. My suggested code would be located in a General
module. Your code would go in the ThisWorkbook module. I'm betting that you placed the Auto_Open procedure in the wrong location. That's why it didn't work for you. lalann wrote: Hi Dave, Many thanks for your reply. However, I use your code, but it still doesn't work. I tried another way, and it works. Private Sub Workbook_Open() Worksheets("Sheet1").Protect Password:="hi", userinterfaceonly:=True Worksheets("Sheet1").EnableOutlining = True End Sub I am not so familiar with code, so I don't know if these two codes mean the same thing. Thanks anyway. "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.) lalann wrote: I need to set a password to protect the worksheet. In the meanwhile, I still should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
protect the worksheet but still can do the "group and outline"
Yes, you are right. I put the code in the wrong location.
Finally it works! Awesome! Thanks so much. "Dave Peterson" wrote: They do the same thing. My suggested code would be located in a General module. Your code would go in the ThisWorkbook module. I'm betting that you placed the Auto_Open procedure in the wrong location. That's why it didn't work for you. lalann wrote: Hi Dave, Many thanks for your reply. However, I use your code, but it still doesn't work. I tried another way, and it works. Private Sub Workbook_Open() Worksheets("Sheet1").Protect Password:="hi", userinterfaceonly:=True Worksheets("Sheet1").EnableOutlining = True End Sub I am not so familiar with code, so I don't know if these two codes mean the same thing. Thanks anyway. "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.) lalann wrote: I need to set a password to protect the worksheet. In the meanwhile, I still should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
protect the worksheet but still can do the "group and outline"
Either version of the code (in the correct locations) will work.
But you don't want both versions running--it won't do much harm, but why do the same thing twice? lalann wrote: Yes, you are right. I put the code in the wrong location. Finally it works! Awesome! Thanks so much. "Dave Peterson" wrote: They do the same thing. My suggested code would be located in a General module. Your code would go in the ThisWorkbook module. I'm betting that you placed the Auto_Open procedure in the wrong location. That's why it didn't work for you. lalann wrote: Hi Dave, Many thanks for your reply. However, I use your code, but it still doesn't work. I tried another way, and it works. Private Sub Workbook_Open() Worksheets("Sheet1").Protect Password:="hi", userinterfaceonly:=True Worksheets("Sheet1").EnableOutlining = True End Sub I am not so familiar with code, so I don't know if these two codes mean the same thing. Thanks anyway. "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.) lalann wrote: I need to set a password to protect the worksheet. In the meanwhile, I still should be able to use "group and outline" in this worksheet. Could anyone help to figure this out? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
| Can I add a "Comment" to an unlocked cell in a protect worksheet | Excel Worksheet Functions | |||
| =IF(VLOOKUP(C11,Group,2,FALSE)=D11,"True","Not Valid") and =IF(D1 | Excel Worksheet Functions | |||
| "Data Group and Outline Group" feature Limited to 8 levels | Excel Discussion (Misc queries) | |||
| How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
Linear Mode
