Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

Hi
Will Data, Validation from the main Excel menu not do it?

regards
Paul
Student wrote:
Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

Use the options under data validation (assume that is what you are using).

--
Regards,
Tom Ogilvy


"Student" wrote:

Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

i am setting the field length to 10 digits there and couldn't add two data
validation rules at once. could I ? if yes then how?

thank you

" wrote:

Hi
Will Data, Validation from the main Excel menu not do it?

regards
Paul
Student wrote:
Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

i am setting the field length to 10 digits there and couldn't add two data
validation rules at once. could I ? if yes then how?

thank you

"Tom Ogilvy" wrote:

Use the options under data validation (assume that is what you are using).

--
Regards,
Tom Ogilvy


"Student" wrote:

Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

It's not clear from the original post whether you need to return an
error message as soon as a user tries to enter text in the cells or if
you want to validate data passed to a VBA routine from those cells.

If you want an error message as soon as a user tries to enter text then
DataValidation from the Excel menu should work as Paul mentioned:
Under the 'Settings' tab input the following:
Allow: 'Custom'
Formula: =ISNUMBER(B11)=TRUE (Assuming B11 is selected)

You could set 'Allow' to Decimal and enter the appropriate restrictions
but this does not seem to capture booleans and errors.

Under the 'Error Alert' tab you can enter an custom error message that
will be displayed when invalid data is entered.

If you wanted to check the values only when a VBA routine is executed
you could try something like:
Sub TextOnly()
For i = 1 To Selection.Rows.Count
CellData = Selection.Cells(i, 1).Value
If Not Application.WorksheetFunction.IsNumber(CellData) Then
MsgBox "The cell does not contain a number"
Exit Sub
End If
Next i
End Sub

Dave Parker

wrote:
Hi
Will Data, Validation from the main Excel menu not do it?

regards
Paul
Student wrote:
Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

use a custom formula for your data validation and then put in a formula that
represents your criteria.

--
Regards,
Tom Ogilvy


"Student" wrote:

i am setting the field length to 10 digits there and couldn't add two data
validation rules at once. could I ? if yes then how?

thank you

"Tom Ogilvy" wrote:

Use the options under data validation (assume that is what you are using).

--
Regards,
Tom Ogilvy


"Student" wrote:

Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated

Sorry I didn't see the followup message before I posted so I didn't
realize you were already using data validation. You can't combine two
built-in validation criteria, but you can combine them using
Allow=Custom.

Let us know exactly how you are setting the field length to 10 digits
using data validation and I'll try to provide more specific help.

Dave Parker


Student wrote:
i am setting the field length to 10 digits there and couldn't add two data
validation rules at once. could I ? if yes then how?

thank you

" wrote:

Hi
Will Data, Validation from the main Excel menu not do it?

regards
Paul
Student wrote:
Good Morning!

I am trying to hard code an error message for invalid data format entered to
a formatted field.

I have selected cells B11 through B30 and set is to positive number with
zero decimal. Now I want to hard code an error message when a text is entered
in these fields.

Is there a way to do this? Please advice
Your afford is greatly appreciated