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.
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.
