Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tabbing thru User-Edit Cells in Protected Sheet ConfusedNHouston Excel Discussion (Misc queries) 2 May 13th 08 11:44 PM
Protected sheet question -- way to allow user to change text alignment and font style? StargateFanFromWork[_3_] Excel Programming 5 September 12th 07 06:10 PM
Relacing default Excel message box when user attempts to change locked cell on protected sheet. Chrisso Excel Programming 2 March 19th 07 05:55 PM
Interact with Spreadsheet Model via the web. [email protected] Excel Programming 0 September 9th 05 03:41 PM
Having a macro interact with a dialog box. Louis[_2_] Excel Programming 2 November 19th 03 05:06 AM


All times are GMT +1. The time now is 06:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"