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

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

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

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