Protect formula without protecting the worksheet
 

I have several areas in my worksheet that I need to protect th
formula's, my spreadsheet also has auto filter therefore I can'
protect the worksheet or the filter won't work.

The end user of this worksheet will also need to have the ablitity t
delete rows.

The area that I need to protect are as follows:
T17:T83
X17:X83
AE17:AE83
AI17:AI83
AK17:AK83
AW17:AW83

AQ1:AU13

OR if it is easier any cell that contains a formula.

Any and all help is appreciated

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55163

Protect formula without protecting the worksheet
 

Hi lostinformulas

protect the worksheet or the filter won't work.
In Excel 2002-2003 you have this option when you protect your sheet manual,
see "Use Autofilter" in the list when you protect your sheet with ToolsProtection..Protect sheet

In older version you can protect your sheet with code
http://www.contextures.com/xlautofilter03.html#Protect

--
Regards Ron De Bruin
http://www.rondebruin.nl



"lostinformulas" <lostinformulas.29cvx0_1150227907.3989@excelforu m-nospam.com wrote in message
news:lostinformulas.29cvx0_1150227907.3989@excelfo rum-nospam.com...

I have several areas in my worksheet that I need to protect the
formula's, my spreadsheet also has auto filter therefore I can't
protect the worksheet or the filter won't work.

The end user of this worksheet will also need to have the ablitity to
delete rows.

The area that I need to protect are as follows:
T17:T83
X17:X83
AE17:AE83
AI17:AI83
AK17:AK83
AW17:AW83

AQ1:AU13

OR if it is easier any cell that contains a formula.

Any and all help is appreciated.


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551634

Protect formula without protecting the worksheet
 

Hi Lostinformulas,

I guess a trick can do it for you.
When you user selects a cell with a formula you switch protection on.
When he/she selects a other cell you switch protection off

Like so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Local Error GoTo Exit_Worksheet_SelectionChange

Dim blnProtect As Boolean

blnProtect = False
If Target.Cells.Count = 1 Then
If Not IsEmpty(Target) Then
If Left(Target.Formula, 1) = "=" Then
blnProtect = True
End If
End If
End If

Exit_Worksheet_SelectionChange:
If blnProtect Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub

HTH,

Executor


lostinformulas wrote:
I have several areas in my worksheet that I need to protect the
formula's, my spreadsheet also has auto filter therefore I can't
protect the worksheet or the filter won't work.

The end user of this worksheet will also need to have the ablitity to
delete rows.

The area that I need to protect are as follows:
T17:T83
X17:X83
AE17:AE83
AI17:AI83
AK17:AK83
AW17:AW83

AQ1:AU13

OR if it is easier any cell that contains a formula.

Any and all help is appreciated.


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551634

Protect formula without protecting the worksheet
 

thanks eyerone.

I guess if I looked at my options under Protect worksheet I would have
known this. I sure glad you guys could help I would have hated for my
boss to tell me this informations.


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551634

Protect formula without protecting the worksheet
 

thanks
I guess if I looked at my options under Protect worksheet I would hav
known this. I sure glad you guys could help I would have hated for m
boss to tell me this informations

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55163