I am currently working on a spreadsheet and several cells I need to have
validation on to only allow entry of either numbers or text only dependant on
which cell it is.

For example, in cell C5 I would like it to only allow text characters (no
numbers) and in C6 I would like it to only accept numbers or spaces (for a
phone number).

I have messed around with various attempts at using just the validation
worksheet functions, but it doesn't fill my requirements (ie for text, it
will still accept numbers so long as there is some text in it somewhere and
for the phone numbers I can't include spaces).

Is there any way I can put some code in to automatically check the entries
of these cells and validate as required?

Hope this all makes sense.

cdb

This is the code I've managed to work out so far, but for some reason it
loops indefinitely. Can anyone help??

Private Sub worksheet_change(ByVal Target As Range)

If Target.Address(False, False) = "C5" Then
Target.Value = UCase(Target.Value)
For X = 0 To 255 'looks at every ascii character
If (X = 65 And X <= 90) Then
'if is between A-Z or 0-9 then do nothing
Else
Target.Value = Replace(Target.Value, Chr(X), "")
'otherwise replace it with nothing
End If
Next X 'so starts at 0 then goes to 1, then 2....
Else
End If
End Sub

TIA,

cdb

"cdb" wrote:

I am currently working on a spreadsheet and several cells I need to have
validation on to only allow entry of either numbers or text only dependant on
which cell it is.

For example, in cell C5 I would like it to only allow text characters (no
numbers) and in C6 I would like it to only accept numbers or spaces (for a
phone number).

I have messed around with various attempts at using just the validation
worksheet functions, but it doesn't fill my requirements (ie for text, it
will still accept numbers so long as there is some text in it somewhere and
for the phone numbers I can't include spaces).

Is there any way I can put some code in to automatically check the entries
of these cells and validate as required?

Hope this all makes sense.

cdb

Afternoon CDB,

I wasn't able to come up with a code that could work however, if you were to
create some type of validation yourself using the condition formatting, that
could work.

Eg. input cell "D1", 2 condition cell "E1" and "E2", E1 and F2 have formulas
"=if($D$10,$D$1,0). Use Cell E2 with formulas (=sum(E1+E2)). Using condition
formatting, state that (=E20) then paint cell red.

Try and see if it works.

Cheers,


"cdb" wrote:

This is the code I've managed to work out so far, but for some reason it
loops indefinitely. Can anyone help??

Private Sub worksheet_change(ByVal Target As Range)

If Target.Address(False, False) = "C5" Then
Target.Value = UCase(Target.Value)
For X = 0 To 255 'looks at every ascii character
If (X = 65 And X <= 90) Then
'if is between A-Z or 0-9 then do nothing
Else
Target.Value = Replace(Target.Value, Chr(X), "")
'otherwise replace it with nothing
End If
Next X 'so starts at 0 then goes to 1, then 2....
Else
End If
End Sub

TIA,

cdb

"cdb" wrote:

I am currently working on a spreadsheet and several cells I need to have
validation on to only allow entry of either numbers or text only dependant on
which cell it is.

For example, in cell C5 I would like it to only allow text characters (no
numbers) and in C6 I would like it to only accept numbers or spaces (for a
phone number).

I have messed around with various attempts at using just the validation
worksheet functions, but it doesn't fill my requirements (ie for text, it
will still accept numbers so long as there is some text in it somewhere and
for the phone numbers I can't include spaces).

Is there any way I can put some code in to automatically check the entries
of these cells and validate as required?

Hope this all makes sense.

cdb