HELP ! How do I protect cells formula but allow data entry

[jay]

Hi there,

I know there is a simple answer but I can't find it.

Problem:
I have a number of cells containing a simple formula of:

=IF(,,"$_____________")

which displays the string "$_____" if there is no data entry.

If an individual enters an incorrect value and decides to change it by
first hitting the "Delete" key, it wipes out the formula !

Question:
How do I protect the cells 'formula' from being deleted and still allow
the data entry person to correct and enter data.

Thanks

J

 

[Earl Kiosterud]

J,

You can't. A cell contains a formula or a value. YOu type your values into
some of the cells, and others contain formulas that give you results.

 

[jay]

If this is the case, how would I get a data entry cell (assume A2R2 for
the data entry and view point, and Z2R2 for the location of the
formula) to appear as;

$__________ (A2R2 when no value is entered)

143 (A2R2 when 143 is entered)

121 (A2R2 when a new value is entered)

$__________ (A2R2 when the 'delete' key was pressed)


Thanks in advance for your guideance !

j

 

[Dave Peterson]

I'm confused about what the formula is and what cell holds that formula.

Can you post it and the cell's address that has the formula and what cell you're
clearing?

 

[jay]

Hi Dave,

Look at my problem in the original post of the message.

the data entry cell is A2B2 (in the example) and assume that a formula
is in Z2B2. The formula would provide the following;

if A2B2 is empty of data, the cell will display $___________
if someone cursors over to A2B2 and enters data, that data will appear
in A2B2. If the user decides to 'delete' the value in A2B2, it is
desired that the value in A2B2 will revert back to the $_____
value (note: '$_____' is just a text string)

Thanks

j

 

[Dave Peterson]

I think Earl understood the question the first time.

But you could use a macro that did the same kind of thing.

Rightclick on the tab for the worksheet that should behave this way. Select
View code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b2")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
If IsEmpty(Target) Then
Application.EnableEvents = False
Target.Value = "$____________"
End If

errHandler:
Application.EnableEvents = True

End Sub

I used B2 as the cell to get the "$_______". Modify that as necessary.

(I thought that A2B2 as a funny R1C1 reference--different language???. But now
I think you mistyped the address. And I'm not sure where Z2B2 is, either.)

 

[jay]

Sorry for the confusion (I'll have more coffee the next time !).

When I used the value A2B2 I meant just "A2" for the data entry point
and cell to be viewed and "Z2" to contain the formula (since I can't
put it in the same cell). My object was the same however.

Thanks and I'll give the VB example a try.

j