Validate Cell Content AfterUpdate
 

I know that the title is hardly clear but I will hope to explain what I mean.
I hope someone can help me, you have been wonderful in the past.

I want to be able to enter a value in a cell and then have it validate that
value and change the content of the cell to the appropriate text. Example:

In cell A1, I enter an "x" and when I hit enter, it changes the text to say
"Removed" or if I enter "y" or leave it blank and hit enter, it changes it to
say "N/A". This is like validation on the go type of thing. Can I write a
built-in formula and have it applied to a range of cells which will then
react the way I want when I enter data into them later?

I hope this is clear, its crystal in my head but not when it comes out in
words. Amazing how that works.

Validate Cell Content AfterUpdate
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sNew As String

If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error GoTo ErrorExit
Select Case UCase(Range("A1").Text)
Case "Y", "": sNew = "N/A"
Case "X": sNew = "Removed"
End Select

If Len(sNew) Then
Application.EnableEvents = False
Range("A1") = sNew
End If
End If

ErrorExit:
Application.EnableEvents = True
End Sub

This "event" code belongs in the relavent sheet module.
Right-click sheet tab View code and paste
When all working OK close the module windows before saving

Regards,
Peter T

"Michael" wrote in message
...
I know that the title is hardly clear but I will hope to explain what I
mean.
I hope someone can help me, you have been wonderful in the past.

I want to be able to enter a value in a cell and then have it validate
that
value and change the content of the cell to the appropriate text. Example:

In cell A1, I enter an "x" and when I hit enter, it changes the text to
say
"Removed" or if I enter "y" or leave it blank and hit enter, it changes it
to
say "N/A". This is like validation on the go type of thing. Can I write a
built-in formula and have it applied to a range of cells which will then
react the way I want when I enter data into them later?

I hope this is clear, its crystal in my head but not when it comes out in
words. Amazing how that works.

Validate Cell Content AfterUpdate
 

Peter,

First of all, thank you so very much for taking the time to help me out.

However, I don't know if I am doing something wrong, although this is pretty
straight forward and I think I am doing it right but absolutely nothing
happens.

Nothing changes, no error even, anything I need to do that I am not doing?

Your assistance is greatly appreciated...

ps. would posting the actual setup of the sheet help you more?

"Peter T" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sNew As String

If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error GoTo ErrorExit
Select Case UCase(Range("A1").Text)
Case "Y", "": sNew = "N/A"
Case "X": sNew = "Removed"
End Select

If Len(sNew) Then
Application.EnableEvents = False
Range("A1") = sNew
End If
End If

ErrorExit:
Application.EnableEvents = True
End Sub

This "event" code belongs in the relavent sheet module.
Right-click sheet tab View code and paste
When all working OK close the module windows before saving

Regards,
Peter T

"Michael" wrote in message
...
I know that the title is hardly clear but I will hope to explain what I
mean.
I hope someone can help me, you have been wonderful in the past.

I want to be able to enter a value in a cell and then have it validate
that
value and change the content of the cell to the appropriate text. Example:

In cell A1, I enter an "x" and when I hit enter, it changes the text to
say
"Removed" or if I enter "y" or leave it blank and hit enter, it changes it
to
say "N/A". This is like validation on the go type of thing. Can I write a
built-in formula and have it applied to a range of cells which will then
react the way I want when I enter data into them later?

I hope this is clear, its crystal in my head but not when it comes out in
words. Amazing how that works.

Validate Cell Content AfterUpdate
 

Peter,

Sorry, but after I wrote the reply I did some debugging and tinckering and
got it to work actually.

Thank you very much for your help,
I owe you one.

Michael

"Peter T" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sNew As String

If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error GoTo ErrorExit
Select Case UCase(Range("A1").Text)
Case "Y", "": sNew = "N/A"
Case "X": sNew = "Removed"
End Select

If Len(sNew) Then
Application.EnableEvents = False
Range("A1") = sNew
End If
End If

ErrorExit:
Application.EnableEvents = True
End Sub

This "event" code belongs in the relavent sheet module.
Right-click sheet tab View code and paste
When all working OK close the module windows before saving

Regards,
Peter T

"Michael" wrote in message
...
I know that the title is hardly clear but I will hope to explain what I
mean.
I hope someone can help me, you have been wonderful in the past.

I want to be able to enter a value in a cell and then have it validate
that
value and change the content of the cell to the appropriate text. Example:

In cell A1, I enter an "x" and when I hit enter, it changes the text to
say
"Removed" or if I enter "y" or leave it blank and hit enter, it changes it
to
say "N/A". This is like validation on the go type of thing. Can I write a
built-in formula and have it applied to a range of cells which will then
react the way I want when I enter data into them later?

I hope this is clear, its crystal in my head but not when it comes out in
words. Amazing how that works.