Trying to force people to not use a comma (even when pasting) or enter a number in a specific format

[dumahim]

Been banging my head against the wall for an hour on this.

I need a way to completely block a comma from being in a row, either typed in or pasted in, and/or force them to only input a valid number that includes 3 decimal places. Again typed or pasted. I've tried a couple things, but there always seems to be a way around it.
The number represents a number of shares and goes out to 3 decimal places. I have the cells formatted to be that way, but we've had some MASSIVE problems over the last few years where someone will either miss entering the period or they hit the comma instead. Because the way the cell is formatted to always go out the 3 decimal places, if they enter a comma or miss the period, it turns something like 256.134 shares into 256134.000 shares.

1) I tried Conditional Formatting to highlight a cell if a comma is entered. Problem is, most of the time this info is being copied from somewhere else and pasting is clearing the conditional formatting.
2) I tried Data Validation to throw an error if a comma is entered, but again, pasting the info in bypasses the Data Validation.

I could apply either of these methods to where they're getting the info from, as we strongly prefer people to be copying and pasting the info to avoid typos, but some people are just going to do their own thing and probably paste into that area anyway. I've seen some VBA solutions, but I'm very much an amateur with that and I'm not following what's being said or how to apply that to my situation. Not to mention, I'm not even sure if copying and pasting will get around that anyway.

So the bottom line is, I need a column to never allow a comma under any circumstance, typed or pasted in and/or only use a number value that already has the decimals there (this would cover the event where people missing the period). Any help with this would be greatly appreciated.

 

[footoo]

Try this (put it in the sheet module) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [A:A]) 'Change column A as required
If Not rng Is Nothing Then
    For Each cel In Target
        If Not IsNumeric(cel) Then
            MsgBox "The data are not all numbers and will not be entered"
            Application.Undo
        Else
            Target.NumberFormat = "###0.000_);[Red](###0.000)"
        End If
    Next
End If
End Sub

This does not cover where the user misses out the decimal point or inputs a comma instead.
(I think this would involve some sort of mind reading.)

 

[footoo]

Revised :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [A:A]) 'Change column A as requir
If Not rng Is Nothing Then
    For Each cel In Target
        If Not WorksheetFunction.IsNumber(cel) Then
            MsgBox "The data are not all numbers and will not be entered"
           Application.EnableEvents=False
           Application.Undo
           Application.EnableEvents=True
        Else
            Target.NumberFormat = "###0.000_);[Red](###0.000)"
        End If
    Next
End If
End Sub

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
    Dim RegEx As Object, Match As Object, cel As Range
    Set RegEx = CreateObject("vbscript.regexp")
    Application.EnableEvents = False
    For Each cel In Target
        With RegEx
            .MultiLine = False
            .Global = True
            .IgnoreCase = False
            .Pattern = "[0-9]\.\d{3}"
        End With
        Set Match = RegEx.Execute(cel)
        If Match.Count <> 1 Then
            MsgBox "Invalid number.  Please enter in this format: #####.###"
            cel.ClearContents
        End If
    Next cel
    Application.EnableEvents = True
End Sub

 

[dumahim]

Thank you. I'll give this a try in the next day or so and confirm it's working.

 

[Peter_SSs]

By my reading of the requirement neither of posts 3/4 cover all circumstances.


OP wants to stop the user accidentally entering "256,134" when they meant to enter "256.134" but post 3 code allows the entry and shows it as "256134.000"

if they enter a comma or miss the period, it turns something like 256.134 shares into 256134.000 shares.

For post 4 if the user enters "123.450", which seems a correct entry with 3 decimal places, the code removes it as an invalid entry.

 

[footoo]

OP wants to stop the user accidentally entering "256,134" when they meant to enter "256.134" but post 3 code allows the entry and shows it as "256134.000"

I pointed this out in post# 2.

 

[dumahim]

Revised :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [A:A]) 'Change column A as requir
If Not rng Is Nothing Then
    For Each cel In Target
        If Not WorksheetFunction.IsNumber(cel) Then
            MsgBox "The data are not all numbers and will not be entered"
           Application.EnableEvents=False
           Application.Undo
           Application.EnableEvents=True
        Else
            Target.NumberFormat = "###0.000_);[Red](###0.000)"
        End If
    Next
End If
End Sub

I changed A:A to B:B as that's the only column to be concerned with. When pasting in 529,807, it did give the error but then got a run-time error '1004' Method of 'Undo' of object' _Application' failed
After that, it seemed to no longer work.

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
    Dim RegEx As Object, Match As Object, cel As Range
    Set RegEx = CreateObject("vbscript.regexp")
    Application.EnableEvents = False
    For Each cel In Target
        With RegEx
            .MultiLine = False
            .Global = True
            .IgnoreCase = False
            .Pattern = "[0-9]\.\d{3}"
        End With
        Set Match = RegEx.Execute(cel)
        If Match.Count <> 1 Then
            MsgBox "Invalid number.  Please enter in this format: #####.###"
            cel.ClearContents
        End If
    Next cel
    Application.EnableEvents = True
End Sub

Same as above, I changed to B:B and did receive the error for invalid number for column B. I also changed the number range to 0-99999. After clicking OK it removed the entry, but this repeated for columns C-G, which shouldn't be affected by this and I'm not understanding why. It also triggers the message if the fields get cleared. When I run my macro to clear the cells, it might be an endless loop, but maybe I just didn't try clicking OK long enough, so maybe there needs to be an exception for blank fields.

Testing the comma, Peter_SSs is correct. It's changing something like 631,281 to 631281.000 which is precisely what I'm trying to prevent. It's likely that it was meant to 631.281, but being that we don't know what people have been entering to lead us to these big problems, we just don't know for sure. It'd be better to just generate the error and clear the cell if a comma is used. I'd say column B should be completely numeric, but I would need to ensure this rule passes by B6 and B42.

I also seem to be able to type in the letters, rather than pasting them, into column B-G and it's allowed, but if I type it in column A, I get Invalid number. I shouldn't be able to type letters in column B.

 

[Peter_SSs]

I pointed this out in post# 2.

Fair enough, though I wasn't sure what "Revised" in the following post was meant to cover/fix.

 

[footoo]

Fair enough, though I wasn't sure what "Revised" in the following post was meant to cover/fix.

I should have repeated the caveat.