forcing to enter a specific format of numbers and symbols in a cell

[sjoerdbosch]

I am looking for a way - preferably hidden in the sheet as a macro - to force users to enter a specific format for latitude and longitude (2 different cells)
The format must be: 00-00.0 N/S - where the 1st 2 digits max 90 separated by - and then a max of 99 separated by . [not comma] and the last digit max 9 followed by a space and N/S should be a choice for North or South
The cell below the latitude is the longitude - 000-00.0 E/W - where the 1st 3 digits is max 180 followed by - and then again the same as latitude and the E/W is a choice for East or West

I have tried data entries and conditional formatting - and some silly macros - none of them work

Please help

 

[bebo021999]

Which cells/range for lati and which for longi input?
Assume A1:F1 for lati and A2:F2 for longi
This code place in worksheet module, trigger any change in both range, then check the input
Right click on sheet name, viewcode, then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lati As Range, longi As Range, cell As Range, s, s1
Set lati = Range("A1:F1") ' change to actual cells
Set longi = Range("A2:F2") ' change to actual cells
If Intersect(Target, Union(lati, longi)) Is Nothing Then Exit Sub
For Each cell In Target
    On Error GoTo z
    s = Split(cell, "-"): s1 = Split(s(1))
    If Not Intersect(cell, lati) Is Nothing Then
        If s(0) > 90 Or s1(0) > 99.9 Or Len(s1(0)) <> 4 Or (s1(1) <> "N" And s1(1) <> "S") Then GoTo z
    Else
        If s(0) > 180 Or s1(0) > 99.9 Or Len(s1(0)) <> 4 Or (s1(1) <> "E" And s1(1) <> "W") Then GoTo z
    End If
    GoTo y
z:
    Application.EnableEvents = False
    cell.ClearContents
    Application.EnableEvents = True
    MsgBox "Must be 90-99.9 N/S (latitude) or 180-99.9 E/W (longitude)"
y:
Next
End Sub

 

[sjoerdbosch]

Thank you for yours
There is no cell range. Just the single cell
I want the user to type the latitude into one cell only (In my case cell F15 for latitude and F16 for longitude - but I need a code where I can change the cell number)
What they have to type in is:
in cell F15 [latitude] : 16-12.3 N and only in that format (so no comma allowed) and for cell F16 [longitude] 088-24.3 W only in that format

my issue is that they type the wrong format using a comma as separator for example.
I want to make that not possible by forcing them to use a decimal point instead.

 

[bebo021999]

Thank you for yours
There is no cell range. Just the single cell
I want the user to type the latitude into one cell only (In my case cell F15 for latitude and F16 for longitude - but I need a code where I can change the cell number)
What they have to type in is:
in cell F15 [latitude] : 16-12.3 N and only in that format (so no comma allowed) and for cell F16 [longitude] 088-24.3 W only in that format

my issue is that they type the wrong format using a comma as separator for example.
I want to make that not possible by forcing them to use a decimal point instead.

So, have you tried to update my code in #2?

....
Set lati = Range("F15") 
Set longi = Range("F16")
....

 

[sjoerdbosch]

Hi
Sorry. But the code does not work.
I have tried to modify it, but nothing seems to work

If you put the code in a worksheet - any empty one will do to check your code.

enter in cell F15 the following: 16-13.2 N
enter in cell F16 the following: 088-10.8 W

you will see the errors
The code should prevent users to enter a different format as the one above.
the max allowed values is not so important - as this would never happen anyway

 

[bebo021999]

It still works for me, or I missed something?
1- Put code in worksheet module

2- Try to input in F15. If text is in different format, a message box appear.
try to input ",", instead of ".":
Before:

After

 

[sjoerdbosch]

not sure what happens.
if i enter the coordinates, the msg box appears and when I press okay, the cells are empty
I see that there is a command for that, but that should only happen when the format entered into the cell is wrong

 

[bebo021999]

If I input correct format:

16-32.5 N
Its OK

But
16-32,5 N
msgbox appears then cell back to empty.

Is it are you looking for?

 

[sjoerdbosch]

yes it is what i am looking for.
but seriously - I have done exactly what you said and when I enter the format as it should be - the box in emptied
have closed excel and reopened it - entered the code again as per above, but it doesn't help at all

 

[sjoerdbosch]

Actually - just tried again
I can input the code as 16-32,2 N and it is entered as such. Same with longitude
If I use the decimal as in 16-35.2 N - it empties the box.
So for some reason there seems to be an error in the input