Validate Numeric Input

Hi

I'm just learning am I'm stuck with validating a UserForm input to ensure a numeric entry in exactly a certain way e.g. 140.235678 or -121.222233 (i.e. 3 places followed by 6 decimal places and could be negative). This is my code below which I know is rubbish but hopefully someone will be kind enough to help. I'm also trying to return an error message to the user if the syntax is not right and also to populate the value to a cell if correct.

Many thanks
Dave

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Since you are new here, on this occasion I have done it for you).

Can there be leading/trailing zeroes and, if so, should they appear in the cell? If they should, do they need to actually be stored in the cell, or can they just be displayed that way?

You say that these are latitude and longitude. However, latitude will never have 3 digits to the left of the decimal (ranging from 0 to +/- 90), so I am guessing that the 3 digit requirement applies only to the longitude results (unless you want to insist on the leading 0 for latitude numbers).

Your profile says you are in New Zealand -- are you expecting all entries to be in/near New Zealand? My old wall map shows New Zealand spanning something from +155 degrees (east) to -165 degrees (west). I would probably set up my data validation to cover the expected numeric range of values (between -165 and -180 or between +155 and +180). Maybe this project considers a broader longitude range. In either case, I would identify the range of allowed values. 3 digits to the left suggests the values must be between -100 and -180 or between +100 and +180.

I would want to understand the requirement to enter 6 digits past the decimal without allowing for trailing zeros. It seems kind of arbitrary to me to insist that a longitude cannot take on the value of, for example, +145.678910. That seems like a perfectly valid longitude to me, so I would want to understand why you would want to declare that entry invalid.

If you disallow the user to use the first entry format and only allow the decimal degree entry, it seems like the data validation need only be to constrain the entry to a value between -90 and +90 for latitude and -180 and +180 for longitude.

If you have an algorithm/procedure that will parse the deg-min-sec-NSEW text string into a signed decimal degree value, then you could test for the same constraints after converting to decimal degree. The main challenge here is, if you don't already have a text to number algorithm/procedure in place, we need to come up with a procedure that can convert the text to a number (I wonder if regular expressions could be useful here?).