Enhance Excel VBA script to include numeric checking
Budget: $30 – $250 USD
I have an existing VBA script that validates and forces maximum field lengths within an Excel worksheet. It works great. This currently works as follows: if there is a row with LENGTHS in column A, then that row identifies maximum field lengths for each column. Cells after that row will have their lengths validated (not the rows before). A cell with a data value that is entered too long will get a popup message and will be truncated to maximum length. I need an enhancement that works as follows.
First, change the identifier row from LENGTHS to FIELDS.
Next, if the FIELDS row of a column contains 'N' or 'n' then that means the column contains numeric data. For example, a row with a FIELDS value of 10N must be 10 characters numeric. If a value is entered that is not numeric, then the user would get a popup message that the text entered is not numeric. This would be the second validation after the maximum length check.
Next, if the FIELDS attributes of a column contains 'A' or 'a' then that means the column contains a numeric amount. For example, a row with a FIELDS value of 10A must be at most ten characters and must be either just numeric or have a value that is similar to 99999.99 (it must end with a period 2 digits). We need to accept the amount either with or without the decimal point. These columns would typically (but not always) be FORMAT CELLS with two decimal places. With this done, Excel tries to format the value as 9999.99 (etc) when it is entered. If you enter 1, it becomes 1.00. If you enter 1.1, it becomes 1.10. However, you can also enter aa, and it is just accepted. This amount validation needs to improve upon that. If a value is entered that is not an amount (not formatted as 99999.99) after it has been formatted by Excel, then the user would get a popup message that the text entered is not a validate amount. Note that either 99999.99 or 9999999 would be accepted. This would be the third validation.
Finally, if the FIELDS attributes of a column contains 'R' or r' then that means the column is required. For example, contains numeric data (0 thru 9). For example, a row with a FIELDS value of 10R must be 10 characters and entry of a value is required. If the cell is empty or contains only spaces, then the user would get a popup message that the text is required. This would be the fourth validation.
These validation rules should be allowed to be combined. For example:
10NR would mean that cells in that column must be 10 characters numeric and is required. 8888 would be accepted but 8888x would not.
10AR would mean that cells in that column must contain up to ten character amount and is required. 8888 or 88.88 would be accepted, but 8888x would not.
First, change the identifier row from LENGTHS to FIELDS.
Next, if the FIELDS row of a column contains 'N' or 'n' then that means the column contains numeric data. For example, a row with a FIELDS value of 10N must be 10 characters numeric. If a value is entered that is not numeric, then the user would get a popup message that the text entered is not numeric. This would be the second validation after the maximum length check.
Next, if the FIELDS attributes of a column contains 'A' or 'a' then that means the column contains a numeric amount. For example, a row with a FIELDS value of 10A must be at most ten characters and must be either just numeric or have a value that is similar to 99999.99 (it must end with a period 2 digits). We need to accept the amount either with or without the decimal point. These columns would typically (but not always) be FORMAT CELLS with two decimal places. With this done, Excel tries to format the value as 9999.99 (etc) when it is entered. If you enter 1, it becomes 1.00. If you enter 1.1, it becomes 1.10. However, you can also enter aa, and it is just accepted. This amount validation needs to improve upon that. If a value is entered that is not an amount (not formatted as 99999.99) after it has been formatted by Excel, then the user would get a popup message that the text entered is not a validate amount. Note that either 99999.99 or 9999999 would be accepted. This would be the third validation.
Finally, if the FIELDS attributes of a column contains 'R' or r' then that means the column is required. For example, contains numeric data (0 thru 9). For example, a row with a FIELDS value of 10R must be 10 characters and entry of a value is required. If the cell is empty or contains only spaces, then the user would get a popup message that the text is required. This would be the fourth validation.
These validation rules should be allowed to be combined. For example:
10NR would mean that cells in that column must be 10 characters numeric and is required. 8888 would be accepted but 8888x would not.
10AR would mean that cells in that column must contain up to ten character amount and is required. 8888 or 88.88 would be accepted, but 8888x would not.