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: http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://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 "Locked" option, & then protect
the sheet.

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

Private Sub Workbook_Open()
Dim Password As String
Password = "secret"
Sheets("sheet1").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 & then the whole Report sheet
can be protected & the Data sheet unprotected for manipulation.

*If your data manipulation is just "some external
data queries, filters and a subtotal function", you should be fine with
sheet protection for the latter two & if the "external data queries"
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 "applied" 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 & assign it to a shortcut key
[alt+F8] - Options, eg:
(change the activesheet.protect... line to options required by
recording your own macro & 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 Sub


hope this helps you with other possibilities,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=529966