I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio

i think you already know how to do the validation bit.
what you ask could be done with vba programming but may be too complex

so i suggest using a combination of 2 cells:

A1 = first cell has validation and allows them to type "ex", maybe other
stuff too

second cell has an IF formula like this
=IF(A1="ex",A1,[your current formula])

or
=IF(A1<"",A1,[your current formula])

and you can protect the second cell to stop them overwriting your formula
--
Allllen


"JP" wrote:

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio

That was the way I was trying to figure it out. It keeps me from losing the
formula, but the problem is getting the formula back into A1 if "Ex" is
deleted.

Maybe it could be done in a similar way with vba? If I copy the formula to
another sheet and protect it, then write the code to allow "Ex," and if it is
deleted, to insert the formula from the protected sheet.

It seems to make sense, but I'm not sure how to write it.

~ Horatio

"Allllen" wrote:

i think you already know how to do the validation bit.
what you ask could be done with vba programming but may be too complex

so i suggest using a combination of 2 cells:

A1 = first cell has validation and allows them to type "ex", maybe other
stuff too

second cell has an IF formula like this
=IF(A1="ex",A1,[your current formula])

or
=IF(A1<"",A1,[your current formula])

and you can protect the second cell to stop them overwriting your formula
--
Allllen


"JP" wrote:

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio

I did some testing, and I found the following code is a start. Now I need to
figure out how to include the entire range. For example, if the contents of
cell A4 are deleted, it is replaced with the formula in Sheet2!A4. Any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Target.Value = "=Sheet2!A1"
End If
End Sub



"Horatio J. Bilge, Jr." wrote:

That was the way I was trying to figure it out. It keeps me from losing the
formula, but the problem is getting the formula back into A1 if "Ex" is
deleted.

Maybe it could be done in a similar way with vba? If I copy the formula to
another sheet and protect it, then write the code to allow "Ex," and if it is
deleted, to insert the formula from the protected sheet.

It seems to make sense, but I'm not sure how to write it.

~ Horatio

"Allllen" wrote:

i think you already know how to do the validation bit.
what you ask could be done with vba programming but may be too complex

so i suggest using a combination of 2 cells:

A1 = first cell has validation and allows them to type "ex", maybe other
stuff too

second cell has an IF formula like this
=IF(A1="ex",A1,[your current formula])

or
=IF(A1<"",A1,[your current formula])

and you can protect the second cell to stop them overwriting your formula
--
Allllen


"JP" wrote:

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio

Hi Horatio

OK - I can see where you are going

What you need is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
'you can change that range a1:a10. this just means
'which area it should detect changes in.

If Target.Value < "Ex" Then
application.enableevents = false
'otherwise you generate a new event here and get
'into an endless cycle

Target.formula = "=[your formula]"
'you can figure out how to write this formula
'in the right way using record macro

application.enableevents = true
end if

End Sub




--
Allllen


"Horatio J. Bilge, Jr." wrote:

I did some testing, and I found the following code is a start. Now I need to
figure out how to include the entire range. For example, if the contents of
cell A4 are deleted, it is replaced with the formula in Sheet2!A4. Any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Target.Value = "=Sheet2!A1"
End If
End Sub



"Horatio J. Bilge, Jr." wrote:

That was the way I was trying to figure it out. It keeps me from losing the
formula, but the problem is getting the formula back into A1 if "Ex" is
deleted.

Maybe it could be done in a similar way with vba? If I copy the formula to
another sheet and protect it, then write the code to allow "Ex," and if it is
deleted, to insert the formula from the protected sheet.

It seems to make sense, but I'm not sure how to write it.

~ Horatio

"Allllen" wrote:

i think you already know how to do the validation bit.
what you ask could be done with vba programming but may be too complex

so i suggest using a combination of 2 cells:

A1 = first cell has validation and allows them to type "ex", maybe other
stuff too

second cell has an IF formula like this
=IF(A1="ex",A1,[your current formula])

or
=IF(A1<"",A1,[your current formula])

and you can protect the second cell to stop them overwriting your formula
--
Allllen


"JP" wrote:

Hello,

If you go to Data|Validation, choose "List" under Allow, type "Ex" (no
quotes) in the "Source" box.

Now you can type "Ex" or choose it from the dropdown box. If you
delete it and re-select the cell, the validation is still there.

Hope this helps,
JP

On Oct 5, 3:55 pm, Horatio J. Bilge, Jr.
wrote:
I have a several ranges of cells that currently have formulas. I wanted to
allow users to enter a specific text ("Ex") in the cells, so I set up data
validation to allow only that text. This works well, except that if the user
then decides to delete the "Ex," the original formula is lost.

Is there a way to allow the text entry, but return the original formula if
the text is deleted?

Thanks,
~ Horatio