software

I want to be able to protect large chunks of my workbook, so that the cells
can't be written over. However I do need to be able to allow some external
data queries, filters and a subtotal funtion to operate (without me taking
the protection on and off every single time).

From the attempts that I've made so far it looks like it isn't possible to
achieve the data manipulation I need with sheet protection. Is this right?

If so, is there any other way that I can protect my reports output without
limiting the ability to view and manipulate the data?


Maybe you can make the book read only? Or does data need to be input
still?--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile: www.excelforum.com/member.php...oamp;userid=25103
View this thread: www.excelforum.com/showthread...hreadid=529966
Hi,
Here are a couple of ideas to consider:

*Are you aware that you can have certain cells unlocked for data entry
when the sheet is protected?
To do this, have your sheet unprotected, select Format - Cells -
Protection, remove the tick from the quot;Lockedquot; option, amp; then protect
the sheet.

*If you are using macros for your other manipulation try:
setting the sheet up with quot;userinterface onlyquot; protection using (in the
thisworkbook module):

Private Sub Workbook_Open()
Dim Password As String
Password = quot;secretquot;
Sheets(quot;sheet1quot;).Protect Password:=Password, _
UserInterFaceOnly:=True
End Sub

*If your reports are summaries of the data they could be designed on a
different sheet to the manipulated data amp; then the whole Report sheet
can be protected amp; the Data sheet unprotected for manipulation.

*If your data manipulation is just quot;some external
data queries, filters and a subtotal functionquot;, you should be fine with
sheet protection for the latter two amp; if the quot;external data queriesquot;
are normal functions such as vlookup or sum etc it should also be okay
(otherwise it may not be possible). With filters, they can be used when
sheet protection is on as long as they have been quot;appliedquot; before the
protection is turned on.

*Reformat your sheet (in conjunction with above) so that data can be
entered without causing a need for changing subtotals etc, (eg use
dynamic ranges).

*Create a macro to unlock/lock the sheet amp; assign it to a shortcut key
[alt F8] - Options, eg:
(change the activesheet.protect... line to options required by
recording your own macro amp; selecting the needed options.)

Sub LockOrUnlockSheet()
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
End If
End Subhope this helps you with other possibilities,
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=529966