Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
Hi guys,
Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
Wicked, thanks Dave, works a treat.
This has been bugging me for a while now. I understand the coding here and have set it up to work ok but is there a way to apply to all the sheets in a work book? I'm happy to write a loop to cycle through the sheets and apply but I don't know if there is a nice clean way of doing it. Cheers Giz "Dave Peterson" wrote: If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
Looping is the way...
Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .select 'see note below .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true End Sub Tom Ogilvy has reported that sometimes protecting sheets will work better if it's selected first. Change the application.goto line to where you want to goto <vbg when the code ends. Gizmo63 wrote: Wicked, thanks Dave, works a treat. This has been bugging me for a while now. I understand the coding here and have set it up to work ok but is there a way to apply to all the sheets in a work book? I'm happy to write a loop to cycle through the sheets and apply but I don't know if there is a nice clean way of doing it. Cheers Giz "Dave Peterson" wrote: If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
I'm a little confused by what is the acutal code and what is part of the
comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where exactly would i code this in? "Dave Peterson" wrote: Looping is the way... Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .select 'see note below .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true End Sub Tom Ogilvy has reported that sometimes protecting sheets will work better if it's selected first. Change the application.goto line to where you want to goto <vbg when the code ends. Gizmo63 wrote: Wicked, thanks Dave, works a treat. This has been bugging me for a while now. I understand the coding here and have set it up to work ok but is there a way to apply to all the sheets in a work book? I'm happy to write a loop to cycle through the sheets and apply but I don't know if there is a nice clean way of doing it. Cheers Giz "Dave Peterson" wrote: If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
Option Explicit
Sub auto_open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets With wks Select Case LCase(.Name) Case Is = LCase("01"), LCase("02"), LCase("03") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End Select End With Next wks End Sub Michelle Thompson wrote: I'm a little confused by what is the acutal code and what is part of the comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where exactly would i code this in? "Dave Peterson" wrote: Looping is the way... Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .select 'see note below .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true End Sub Tom Ogilvy has reported that sometimes protecting sheets will work better if it's selected first. Change the application.goto line to where you want to goto <vbg when the code ends. Gizmo63 wrote: Wicked, thanks Dave, works a treat. This has been bugging me for a while now. I understand the coding here and have set it up to work ok but is there a way to apply to all the sheets in a work book? I'm happy to write a loop to cycle through the sheets and apply but I don't know if there is a nice clean way of doing it. Cheers Giz "Dave Peterson" wrote: If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mofiying the protect option.
Option Explicit
Sub auto_open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets(Array _ ("01", "02", "03")) With wks .Select 'see note below .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With Next wks Application.Goto ThisWorkbook.Worksheets(1).Range("a1"), Scroll:=True End Sub Code to be entered in a general/standard module. Gord Dibben MS Excel MVP On Fri, 4 Dec 2009 13:08:01 -0800, Michelle Thompson wrote: I'm a little confused by what is the acutal code and what is part of the comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where exactly would i code this in? "Dave Peterson" wrote: Looping is the way... Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .select 'see note below .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true End Sub Tom Ogilvy has reported that sometimes protecting sheets will work better if it's selected first. Change the application.goto line to where you want to goto <vbg when the code ends. Gizmo63 wrote: Wicked, thanks Dave, works a treat. This has been bugging me for a while now. I understand the coding here and have set it up to work ok but is there a way to apply to all the sheets in a work book? I'm happy to write a loop to cycle through the sheets and apply but I don't know if there is a nice clean way of doing it. Cheers Giz "Dave Peterson" wrote: If you already have the outline/subtotals 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 End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gizmo63 wrote: Hi guys, Is there any way of modifying the 'worksheet - protect' function, maybe using startup VBA, that will let users open and close grouped columns and rows but will still protect all the formulas and those rows that are truly hidden. I really really need an answer to this, even if it's from an MVP, gold/ silver rated user who can uneqivocally say it cannot be done. I know I can get round it by putting macros to show/hide the groups but i'd rather not get this complicated. Many thanks - Giz -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkboxes vs. Option Buttons | Excel Discussion (Misc queries) | |||
No option to password protect opening an excel / word file | Excel Discussion (Misc queries) | |||
How to protect option button? | Excel Worksheet Functions | |||
Filter option | Excel Discussion (Misc queries) | |||
How to protect Option button in worksheet | Excel Worksheet Functions |