Thread: Protect formula without protecting the worksheet

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.

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 Tools>Protection..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@excelforum-nospam.com> wrote in message
news:lostinformulas.29cvx0_1150227907.3989@excelforum-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
>

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

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.

thanks
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.