Force a . separator

[MikeGozna]

Hi all, I am very new to Excel. I have made a holiday planner which seems to work ok.
I am now putting a few safetys into it. What I want to do is when a user enters a number of hours into a data form box in the wrong format, say 8,5 instead of 8.5 a error box comes up that will not let the operation continue until the . is present. Can anyone assist please
Im sorry this is a bit long wnided but Im not sure on the correct termonology yet, but im learning. Thanks in advane.
PS are there any banks of code that I could view and take the bits I think will work for me,or re-work the code to suit my needs, Thanks again Mike G.

[Leith Ross]

Hello MikeGonza,

Welcome ot the Forum!

The answer depends on what type of "form" you are referring to. Is this a worksheet or a VBA UserForm?

[NBVC]

You can use data validation for this possibly.

Go to Data|Validation

Select Decimal from the Allow menu

Select Between from the Data menu

Enter a Min and Max value

You can then click the Input and Error tabs to customize the input prompt and Error alert.

This will force the user to use a "real number" between your Min and Max and can include whole numbers or decimals, but not commas...

[MikeGozna]

Hello MikeGonza,

Welcome ot the Forum!

The answer depends on what type of "form" you are referring to. Is this a worksheet or a VBA UserForm?

Yes thanks Leith
I enter the Hours into a user form so the Data validation answer below would work, but I need to put it into my code when I exit the user form and enter the data into the cell.
any thoughts?

[Leith Ross]

Hello Mike,

In the hours TextBox Exit event you can set Cancel = True to force the user back to the TextBox. You can first display a message using the MsgBox to alert the user the entry is not valid.

[MikeGozna]

You can use data validation for this possibly.

Go to Data|Validation

Select Decimal from the Allow menu

Select Between from the Data menu

Enter a Min and Max value

You can then click the Input and Error tabs to customize the input prompt and Error alert.

This will force the user to use a "real number" between your Min and Max and can include whole numbers or decimals, but not commas...

Thanks NBVC
This would work fine, but the Hours are being entered into a VBA user form, so I need this to work in my exit code as the data is entered into the spreadsheet, see my answer above. Thankyou very much for the quick response thou. MikeG