Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking certain cells
I have a spreadsheet that as a group of 5 columns grouped together using the
DATA - GROUP option - I want to protect the first colum from entry and allow figures to be inputted into the other 4 columns - I can do this if I dont mind seeing all the other columns but I want to collapse them as necessary but if I protect the relevant cells and then Protect the Sheet it wont let me collapse the group. Is it possible? Thanks Beverly |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking certain cells
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.) Beverly Darvill wrote: I have a spreadsheet that as a group of 5 columns grouped together using the DATA - GROUP option - I want to protect the first colum from entry and allow figures to be inputted into the other 4 columns - I can do this if I dont mind seeing all the other columns but I want to collapse them as necessary but if I protect the relevant cells and then Protect the Sheet it wont let me collapse the group. Is it possible? Thanks Beverly -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking certain cells
Dave
do I put this code in as a macro after I protected the cells I want protected? And how would I reset it each time - sorry to ask such silly questions but this is outside of my use of excel. thanks Beverly "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.) Beverly Darvill wrote: I have a spreadsheet that as a group of 5 columns grouped together using the DATA - GROUP option - I want to protect the first colum from entry and allow figures to be inputted into the other 4 columns - I can do this if I dont mind seeing all the other columns but I want to collapse them as necessary but if I protect the relevant cells and then Protect the Sheet it wont let me collapse the group. Is it possible? Thanks Beverly -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking certain cells
You can put the code in anytime you want <bg, but you have to put it in a
certain spot. Put it in a general module (Insert|Module when you're in the VBE). But this code has to run each time the workbook is opened. It's not one of those run once and forget about it. So that means that you (and your users) have to allow macros to run each time the workbook is opened. After you've put the code in, you can save the file (as a new name if you're unsure) and then close and reopen that file. The Auto_Open procedure should run (if your security settings are ok--and you allow macros to run). Then you can test showing/hiding rows. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side. Paste the code in there. Then alt-f11 (to get back to excel), close and save the workbook. Then File|Open the newly saved workbook. Did it work??? Beverly Darvill wrote: Dave do I put this code in as a macro after I protected the cells I want protected? And how would I reset it each time - sorry to ask such silly questions but this is outside of my use of excel. thanks Beverly "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.) Beverly Darvill wrote: I have a spreadsheet that as a group of 5 columns grouped together using the DATA - GROUP option - I want to protect the first colum from entry and allow figures to be inputted into the other 4 columns - I can do this if I dont mind seeing all the other columns but I want to collapse them as necessary but if I protect the relevant cells and then Protect the Sheet it wont let me collapse the group. Is it possible? Thanks Beverly -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking Cells | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
Locking Cells | Excel Worksheet Functions | |||
Locking Cells | Excel Discussion (Misc queries) | |||
Locking certain cells | Excel Worksheet Functions |