Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protected - user cannot interact
I have devised a macro that protects a worksheet so that data can only be
modified via the macro user form. This works fine however the users cannot interact with the data as I would like, for example expand and collapse groups, as it would seem that protecting the worksheet disables this ability. Is there any way to protect the data but not exclude the user from doing such basic things? Any help greatly appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protected - user cannot interact
Have a look at the arguments that you can pass on the protect
expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables) "JLR-Mart" wrote: I have devised a macro that protects a worksheet so that data can only be modified via the macro user form. This works fine however the users cannot interact with the data as I would like, for example expand and collapse groups, as it would seem that protecting the worksheet disables this ability. Is there any way to protect the data but not exclude the user from doing such basic things? Any help greatly appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protected - user cannot interact
Unless I'm missing something here, there doesn't appear to be an argument
that controls this function. Anyone? "nathan_savidge" wrote: Have a look at the arguments that you can pass on the protect expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables) "JLR-Mart" wrote: I have devised a macro that protects a worksheet so that data can only be modified via the macro user form. This works fine however the users cannot interact with the data as I would like, for example expand and collapse groups, as it would seem that protecting the worksheet disables this ability. Is there any way to protect the data but not exclude the user from doing such basic things? Any help greatly appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protected - user cannot interact
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.) JLR-Mart wrote: I have devised a macro that protects a worksheet so that data can only be modified via the macro user form. This works fine however the users cannot interact with the data as I would like, for example expand and collapse groups, as it would seem that protecting the worksheet disables this ability. Is there any way to protect the data but not exclude the user from doing such basic things? Any help greatly appreciated -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tabbing thru User-Edit Cells in Protected Sheet | Excel Discussion (Misc queries) | |||
Protected sheet question -- way to allow user to change text alignment and font style? | Excel Programming | |||
Relacing default Excel message box when user attempts to change locked cell on protected sheet. | Excel Programming | |||
Interact with Spreadsheet Model via the web. | Excel Programming | |||
Having a macro interact with a dialog box. | Excel Programming |