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.

 

[felixstraube]

What other solution would you suggest if VBA won't work for this?

If you copy and paste data, I would make sure that the data is in the right format before pasting.
Mabe something like this?:

You take 1 value and paste it in A2 (helper sheet) and check if columns B and C are correct. If everything is correct you paste the whole data set:

Book5.xlsx
	A	B	C
1	Data Sample	Integer part	Decimals
2	112,356.56	112356	.56
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INT(A2)
C2	C2	=MOD(A2,1)

In my example i took a value copied from a text file as "112,356.56".
If i have "," as thousand separator and "." as decimal separator all works fine and i get:

But if i have "." as thousand separator and "," as decimal separator i get:

Maybe if you tell us a little more of you data entry process we can think of something better.

 

[dumahim]

If you copy and paste data, I would make sure that the data is in the right format before pasting.
Mabe something like this?:

You take 1 value and paste it in A2 (helper sheet) and check if columns B and C are correct. If everything is correct you paste the whole data set:

Book5.xlsx
	A	B	C
1	Data Sample	Integer part	Decimals
2	112,356.56	112356	.56
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INT(A2)
C2	C2	=MOD(A2,1)

In my example i took a value copied from a text file as "112,356.56".
If i have "," as thousand separator and "." as decimal separator all works fine and i get:

View attachment 115697

But if i have "." as thousand separator and "," as decimal separator i get:
View attachment 115698

Maybe if you tell us a little more of you data entry process we can think of something better.

Could have sworn I pasted an example previously, but I'm not seeing it now. I can't attach a screenshot from work, so I'll do this for now and try to post an example when I get home and have more freedom.

Column A will be a number. It's basically irrelevant, so don't need it checked.
Column B will be a number with a decimal point out 3 spaces. The code provided that seems to be working as intended here. Pattern = "[0-99999]\.\d{3}"
Columns C-E are text and irrelevant.
Column F is a mutual fund CUSIP, so it's always 9 characters, alpha numeric. I'm not concerned about this field because if the user gets it wrong, it won't work, so there's no risk there.
Column G is another number of varying length but ultimately irrelevant.

I like what mumps provided on page 1, but it's checking all the fields rather than just column B and also goes into an endless loop of giving the error message when I use the ClearContents macro. I think if I can get a fix for these two things I'd be all set.

 

[Peter_SSs]

See how this one goes.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim sTestVal As String, sErr As String
  
  Set Changed = Intersect(Target, Columns("A"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Set RX = CreateObject("VBScript.RegExp")
    RX.Pattern = "^\d+\.\d{3}$"
    For Each c In Changed
      If Len(c.Value) > 0 Then
        sTestVal = IIf(InStr(1, c.Value, ".") = 0, c.Value, Format(c.Value, "0.000"))
        If Not RX.Test(sTestVal) Or Len(c.Value) - InStr(1, c.Value & ".", ".") > 3 Then
          sErr = sErr & vbLf & c.Address(0, 0) & vbTab & c.Value
          c.ClearContents
        End If
      End If
    Next c
    Application.EnableEvents = True
    If Len(sErr) > 0 Then MsgBox "Invalid values have been removed from the following cell(s):" & vbLf & Mid(sErr, 2)
  End If
End Sub