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

The problem I see is that the formula in each cell is different.
For example, the formula in:
Sheet1!E5 = "=IF(D5=0,"",RANK(D5,Sheet2!$A$2:$A$7,1))"
and the formula in:
Sheet1!E11 = "=IF(D11=0,"",RANK(D11,Sheet2!$B$2:$B$7,1))"

I plan to save those original formulae in Sheet3. Then if a user changes
Sheet1!E5 to "Ex" (which I allowed through data validation), and later
decides to delete the "Ex," the cell will acquire the formula from Sheet3!E5.
Similarly, if a user changes Sheet1!E11, the cell will acquire the formula
from Sheet3!E11, and so on.


"Allllen" wrote:

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

Use the target cell's row and column numbers to get the correct formula.
For example:

Target.Formula = Sheets("Sheet3").Cells(Target.Row,
Target.Column).Formula


Horatio wrote:
The problem I see is that the formula in each cell is different.
For example, the formula in:
Sheet1!E5 = "=IF(D5=0,"",RANK(D5,Sheet2!$A$2:$A$7,1))"
and the formula in:
Sheet1!E11 = "=IF(D11=0,"",RANK(D11,Sheet2!$B$2:$B$7,1))"

I plan to save those original formulae in Sheet3. Then if a user changes
Sheet1!E5 to "Ex" (which I allowed through data validation), and later
decides to delete the "Ex," the cell will acquire the formula from Sheet3!E5.
Similarly, if a user changes Sheet1!E11, the cell will acquire the formula
from Sheet3!E11, and so on.


"Allllen" wrote:


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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

That works well, but I have one glitch. There are a few cells in the target
range that are merged cells.

In normal (non-merged) cells, the code works great, but in the merged cells,
there is a quirk. When I enter "Ex" it works fine. If I enter a different
value (e.g., "Not Ex"), the cell correctly returns the formula from Sheet3.
But if I delete the value from the cell, I get an error.

Here is the code I am using:
If Target.Value < "Ex" Then
Application.EnableEvents = False
Target.Formula = Sheets("Sheet3").Cells(Target.Row, Target.Column).Formula
Application.EnableEvents = True
End If


"Debra Dalgleish" wrote:

Use the target cell's row and column numbers to get the correct formula.
For example:

Target.Formula = Sheets("Sheet3").Cells(Target.Row,
Target.Column).Formula


Horatio wrote:
The problem I see is that the formula in each cell is different.
For example, the formula in:
Sheet1!E5 = "=IF(D5=0,"",RANK(D5,Sheet2!$A$2:$A$7,1))"
and the formula in:
Sheet1!E11 = "=IF(D11=0,"",RANK(D11,Sheet2!$B$2:$B$7,1))"

I plan to save those original formulae in Sheet3. Then if a user changes
Sheet1!E5 to "Ex" (which I allowed through data validation), and later
decides to delete the "Ex," the cell will acquire the formula from Sheet3!E5.
Similarly, if a user changes Sheet1!E11, the cell will acquire the formula
from Sheet3!E11, and so on.


"Allllen" wrote:


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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

The glitch only applies if I select the cell and press the delete key. If I
select the cell, press the backspace key, and then the enter key, it works
fine. Is there a tweak that I can make to the code?


"Horatio J. Bilge, Jr." wrote:

That works well, but I have one glitch. There are a few cells in the target
range that are merged cells.

In normal (non-merged) cells, the code works great, but in the merged cells,
there is a quirk. When I enter "Ex" it works fine. If I enter a different
value (e.g., "Not Ex"), the cell correctly returns the formula from Sheet3.
But if I delete the value from the cell, I get an error.

Here is the code I am using:
If Target.Value < "Ex" Then
Application.EnableEvents = False
Target.Formula = Sheets("Sheet3").Cells(Target.Row, Target.Column).Formula
Application.EnableEvents = True
End If


"Debra Dalgleish" wrote:

Use the target cell's row and column numbers to get the correct formula.
For example:

Target.Formula = Sheets("Sheet3").Cells(Target.Row,
Target.Column).Formula


Horatio wrote:
The problem I see is that the formula in each cell is different.
For example, the formula in:
Sheet1!E5 = "=IF(D5=0,"",RANK(D5,Sheet2!$A$2:$A$7,1))"
and the formula in:
Sheet1!E11 = "=IF(D11=0,"",RANK(D11,Sheet2!$B$2:$B$7,1))"

I plan to save those original formulae in Sheet3. Then if a user changes
Sheet1!E5 to "Ex" (which I allowed through data validation), and later
decides to delete the "Ex," the cell will acquire the formula from Sheet3!E5.
Similarly, if a user changes Sheet1!E11, the cell will acquire the formula
from Sheet3!E11, and so on.


"Allllen" wrote:


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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html