Enhance editing process within existing Excel VBA script
Budget: $30 – $250 USD
This Excel VBA script is used to validate data that is being entered into cells. The validation is based on a FIELDS row that must be added to the spreadsheet that defines the attributes for the cells that subsequently appear in that column. The validations that are supported are length, alpha, numeric, amount, and required. For example, the attribute on a column might be 10R (ten characters and a value is required), 10N (up to ten characters numeric where the cell value is optional), or 10AR (up to ten characters as an amount field where the cell value is required),
This script is currently about 280 lines of VBA. Two changes must be made.
First is that the R (required) function is rejecting a field when it contains an embedded blank. For example, a cell value of "tom smith" should be accepted but it is being rejected with the required error, even though the cell is populated.
Second is that the errors are loaded in an adjacent (ERRORS) sheet, with multiple columns for each error type. A given cell might be identified more than once within the errors. We need this changed so that all errors appear in Column A and that a given cell is only reported with a single error message. Having all of the errors in Column A will make it easier to identify and resolve the problems. Having a field cell only reported once will also be a good improvement.
I will provide a sample XLSM with the VBA script once the project is assigned. The VBA script within that XLSM will need to be the starting point for this effort.
This script is currently about 280 lines of VBA. Two changes must be made.
First is that the R (required) function is rejecting a field when it contains an embedded blank. For example, a cell value of "tom smith" should be accepted but it is being rejected with the required error, even though the cell is populated.
Second is that the errors are loaded in an adjacent (ERRORS) sheet, with multiple columns for each error type. A given cell might be identified more than once within the errors. We need this changed so that all errors appear in Column A and that a given cell is only reported with a single error message. Having all of the errors in Column A will make it easier to identify and resolve the problems. Having a field cell only reported once will also be a good improvement.
I will provide a sample XLSM with the VBA script once the project is assigned. The VBA script within that XLSM will need to be the starting point for this effort.