VB Script for handling Excel Save with Cell Validation
I have never written VB code before and everything that I am seeing during research is telling me this is the only way to get this to work.
I have an excel spreadsheet with 81 columns and each column with some type of validation, either a list or date or required to have something in the cell. The problem is most are copying and pasting their data into the spreadsheet which deletes or invalidates any of the cell validation and still allows them to save.
I need for them to be able to still paste, but the validation must still work not allowing them to save the document until they have corrected all their errors. I was told that I should write some VBA code to hook the file-save event and either do the validation work in VBA or trigger the cell validation. If any cell fails validation, I would cancel the save and highlight the bad cell.
I have an excel spreadsheet with 81 columns and each column with some type of validation, either a list or date or required to have something in the cell. The problem is most are copying and pasting their data into the spreadsheet which deletes or invalidates any of the cell validation and still allows them to save.
I need for them to be able to still paste, but the validation must still work not allowing them to save the document until they have corrected all their errors. I was told that I should write some VBA code to hook the file-save event and either do the validation work in VBA or trigger the cell validation. If any cell fails validation, I would cancel the save and highlight the bad cell.
The_Barman is right. Pasting data into a workbook isn't the bet solution. Neither is validation at Save or Close time. Imagine just 10 rows being checked by a program that runs on Close which highlights the invalid cell and cancels the Close process. If only 10 invalid entries should be found out of the 810 having been made that would be a low error quota of just over 1% but a major disturbance to the user who pressed the Close button innocently enough, with his mind already on the next task.
The alternative to pasting is an import program that picks the data from the source and writes to the workbook after validation. When an invalid item is found the program might try to coerce the entry failing which an error message would pop up to the effect that the entry in column, say AD, should be a date but isn't, and please enter the correct date. This could prove awkward, especially with the large variety of possible error types in 81 columns, and that's why The_Barman's suggestion to use a form would be preferable. Instead of importing a cell value at a time the import program should get one data set, write it to the form, where it can be validated, edited and modified before being written to the workbook at the press of a button. In this scenario the same form would be used for individual, manual entries and en masse data imported from other sources.
The alternative to pasting is an import program that picks the data from the source and writes to the workbook after validation. When an invalid item is found the program might try to coerce the entry failing which an error message would pop up to the effect that the entry in column, say AD, should be a date but isn't, and please enter the correct date. This could prove awkward, especially with the large variety of possible error types in 81 columns, and that's why The_Barman's suggestion to use a form would be preferable. Instead of importing a cell value at a time the import program should get one data set, write it to the form, where it can be validated, edited and modified before being written to the workbook at the press of a button. In this scenario the same form would be used for individual, manual entries and en masse data imported from other sources.
ASKER
Here is the copy of the sheet...its been a very very long time since I did anything with the forms. This is something that was inherited and my ability to make too many formatting or big changes is very very limited.
EE-COPY.xlsx
EE-COPY.xlsx
@leerljr68,
I see that there are very few validations. Is it that you wish to validate only those cells that have validation lists?
I still see a problem with the idea you have suggested because if a message pops up telling you that H17 should be M or F but isn't, and which one should it be, you would need column C and D (name and first name) to make a decision, unless the name is Chinese and you can't tell. So, C and D could be added to all error messages. Would that suffice for all possible error messages? In short, you should work out a schedule of which cells to validate. how to validate each cell, and how the program should respond to each error you specify.
You are right, of course, in that creating a form to work like I described earlier requires quite a lot of effort in addition to some change from your side. Going with your original idea might be more realistic.
For that, it is also necessary to find a way to differentiate between old entries and new, in addition to a clear and comprehensive description of what constitutes an error and how each error should be handled. If there aren't all that many rows in your sheet all entries could be validated before each save. Otherwise one might just do the last 10 or 20 entries or entries of today's date or the past 3 days, if there are dates. Your input would be extremely useful.
Another question. I see that your sheet features an elaborate color scheme. Would posted data destroy the scheme so that it needs to be restored at the time of validation?
I see that there are very few validations. Is it that you wish to validate only those cells that have validation lists?
I still see a problem with the idea you have suggested because if a message pops up telling you that H17 should be M or F but isn't, and which one should it be, you would need column C and D (name and first name) to make a decision, unless the name is Chinese and you can't tell. So, C and D could be added to all error messages. Would that suffice for all possible error messages? In short, you should work out a schedule of which cells to validate. how to validate each cell, and how the program should respond to each error you specify.
You are right, of course, in that creating a form to work like I described earlier requires quite a lot of effort in addition to some change from your side. Going with your original idea might be more realistic.
For that, it is also necessary to find a way to differentiate between old entries and new, in addition to a clear and comprehensive description of what constitutes an error and how each error should be handled. If there aren't all that many rows in your sheet all entries could be validated before each save. Otherwise one might just do the last 10 or 20 entries or entries of today's date or the past 3 days, if there are dates. Your input would be extremely useful.
Another question. I see that your sheet features an elaborate color scheme. Would posted data destroy the scheme so that it needs to be restored at the time of validation?
ASKER
Not sure how you are seeing very few validations...there are quite a few columns (all with headers in white...the gray header columns are not required). The rest of the columns were set to have at least 2 characters in the cell and a few specific validations regarding a date or the list.
Most of this data is cut and pasted by the end users and it is submitted on a monthly basis...meaning the new file for each month would be for the past month only, so no issue with trying to differentiate new from old values. Very rarely are they going to tab through the line and do a fill in. As far as some of the list fields, the end users have used the form enough to know what specific value should be in the cell so I'm not worried about if they put Male in the gender field and it's supposed to only be M. They should already know that and if they see that cell is in error, they will know it needs to be an M instead of being spelled out.
The color scheme is irrevelent, if it doesn't keep, it doesn't keep. I'm moreso concerned about the data being completed correctly and submitted. The problem is that when end users submit this sheet and there are tons of wrong/incomplete/bad data in the cells, someone has to spend hours trying to fix it. Once the sheet is correct, it gets imported into an Access Dbase.
Hopefully that assists in more background to what I'm dealing with. But the fact still remains, I don't know VB. Hahahaha.
Most of this data is cut and pasted by the end users and it is submitted on a monthly basis...meaning the new file for each month would be for the past month only, so no issue with trying to differentiate new from old values. Very rarely are they going to tab through the line and do a fill in. As far as some of the list fields, the end users have used the form enough to know what specific value should be in the cell so I'm not worried about if they put Male in the gender field and it's supposed to only be M. They should already know that and if they see that cell is in error, they will know it needs to be an M instead of being spelled out.
The color scheme is irrevelent, if it doesn't keep, it doesn't keep. I'm moreso concerned about the data being completed correctly and submitted. The problem is that when end users submit this sheet and there are tons of wrong/incomplete/bad data in the cells, someone has to spend hours trying to fix it. Once the sheet is correct, it gets imported into an Access Dbase.
Hopefully that assists in more background to what I'm dealing with. But the fact still remains, I don't know VB. Hahahaha.
No problem. I'll provide the VBA knowledge. Let me teach you a little about the process so as to facilitate our communication. The program will look at ever cell, addressing it by (column) name and checking its credentials which are in a list of credentials. For example, ...
If your name is A the content must be one of 5 acceptable values (which?). Accept upper & lower case. Replace existing with required (all caps).
If your name is B the content must have at least 2 characters, no numbers.
...
If your name is H the content must commence with f or m or F or M. If it does, replace it with M or F as indicated.
....
This goes on for 81 cells, and right now the question is which one of us will sit down for two hours and write down the requirements. I think not only that you will produce a much better list than I can deduce from the worksheet you posted, not to mention that the review will lead to improvements (especially, since you can raise your requirements vis-a-vis worksheet validation), but also that you can't really do the job without it because after everything is programmed you will need the list in order to check the program before swearing by it. Last, but not least, it looks like fair division of labour, if you specify what you want and I do the work. :-)
If your name is A the content must be one of 5 acceptable values (which?). Accept upper & lower case. Replace existing with required (all caps).
If your name is B the content must have at least 2 characters, no numbers.
...
If your name is H the content must commence with f or m or F or M. If it does, replace it with M or F as indicated.
....
This goes on for 81 cells, and right now the question is which one of us will sit down for two hours and write down the requirements. I think not only that you will produce a much better list than I can deduce from the worksheet you posted, not to mention that the review will lead to improvements (especially, since you can raise your requirements vis-a-vis worksheet validation), but also that you can't really do the job without it because after everything is programmed you will need the list in order to check the program before swearing by it. Last, but not least, it looks like fair division of labour, if you specify what you want and I do the work. :-)
ASKER
That sounds like an excellent plan for resolution...and more importantly fair! :) Let me get some time to do this on Monday/Tuesday and will post. I can't thank you and experts-exchange enough.
ASKER
FAUSTULUS, see the attached document with each columns requirements. Those columns that are required, it basically needs to have at least a character in it. Let me know if any questions or something is unclear.
Please pardon the upcoming delay. I'm travelling and expect to be able to look into your workbook toward the end of this week.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost through testing the first line and almost got it. I'm getting an error boxes (stoppage) on each of the following cells:
Case 25, 26, 30, 31, 37, 38, 41, 42, 45, 46
' columns Y, Z, AD, AE, AK, AL, AO, AP,
' AS, AT
If Not (EntryIsDate(Cell) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 56, 63, 70 ' column BD, BK, BR
If Not (IsLongNumber(Cell, 10, NwsExact) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 60, 67, 74 ' columns BH, BO, BV
If Not (IsStateCode(Cell) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 61, 68, 75 ' column BI, BP, BW
If Not (IsLongNumber(Cell, 5, NwsMin) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
These are the cells that are not required, can be blank, but must also be validated as a date, or state code, or specific number length.
Everything else seems to be okay at this point.
lrl
Case 25, 26, 30, 31, 37, 38, 41, 42, 45, 46
' columns Y, Z, AD, AE, AK, AL, AO, AP,
' AS, AT
If Not (EntryIsDate(Cell) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 56, 63, 70 ' column BD, BK, BR
If Not (IsLongNumber(Cell, 10, NwsExact) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 60, 67, 74 ' columns BH, BO, BV
If Not (IsStateCode(Cell) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 61, 68, 75 ' column BI, BP, BW
If Not (IsLongNumber(Cell, 5, NwsMin) Or _
Len(Trim(Cell.Value)) = 0) Then Exit Function
These are the cells that are not required, can be blank, but must also be validated as a date, or state code, or specific number length.
Everything else seems to be okay at this point.
lrl
Hi,
Yes, there is an error in the concatenation of the conditions in the lines you picked out. Please repalce the entire procedure with this revised one.
Yes, there is an error in the concatenation of the conditions in the lines you picked out. Please repalce the entire procedure with this revised one.
Function EntryValidation() As Boolean
Dim Ws As Worksheet
Dim Cell As Range
Dim R As Long, C As Long
Set Ws = ThisWorkbook.Worksheets("Sheet1") ' modify sheet name as required
R = NwsFirstDataRow
With Ws
Do While Application.WorksheetFunction.CountA(.Rows(R))
For C = 1 To .Cells(1, 1).CurrentRegion.Columns.Count
Set Cell = .Cells(R, C)
Select Case C
Case 1 ' column A
If Not CheckCategory(Cell) Then Exit Function
Case 3, 13 ' columns C, M
If Not NoBlankNoNumber(Cell) Then Exit Function
Case 4 ' column D
If Not NoBlankNoNumber(Cell, True) Then Exit Function
Case 5 ' column E
If Not NoBlankNoNumber(Cell, True, 1) Then Exit Function
Case 8 ' column H
If Not NoBlankNoNumber(Cell, True, 1) Then Exit Function
Case 9, 15, 20, 21, 33, 34, 76
' columns I, O, T, U, AG, AH, BZ
If Not EntryIsDate(Cell) Then Exit Function
Case 10, 47 ' columns J, AW
If Not IsLongNumber(Cell, 10, NwsExact) Then Exit Function
Case 11 ' column K
If Not IsLongNumber(Cell, 6, NwsMax) Then Exit Function
Case 14, 17, 18, 19, 32, 45, 46, 48 To 50, 74, 75, 78, 81, 82
' columns N, Q, R, S, AF, AU, AV, AX, AY,
' AZ, BX, BY, CB, CE, CD
If Not HasValue(Cell) Then Exit Function
Case 25, 26, 30, 31, 37 To 40, 43, 44, 79, 80
' columns Y, Z, AD, AE, AK, AL, AO, AP,
' AS, CC, CD
If (Not EntryIsDate(Cell)) Or _
(Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 51 ' column BA
If Not IsStateCode(Cell) Then Exit Function
Case 52 ' column BB
If Not IsLongNumber(Cell, 5, NwsMin) Then Exit Function
Case 54 ' column BB
If (Not IsLongNumber(Cell, 10, NwsExact)) Or _
(Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 58, 65, 72 ' columns BH, BO, BV
If (Not IsStateCode(Cell)) Or _
(Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 59, 66, 73 ' column BI, BP, BW
If (Not IsLongNumber(Cell, 5, NwsMin)) Or _
(Len(Trim(Cell.Value)) = 0) Then Exit Function
Case 61, 68 ' column BK, BR
If (Not IsLongNumber(Cell, 10, NwsExact)) Or _
(Len(Trim(Cell.Value)) = 0) Then Exit Function
End Select
Next C
R = R + 1
Loop
End With
MsgBox "No errors were found." & vbCr & _
"This workbook is now being saved.", vbInformation, _
"Confirmation"
EntryValidation = True
End FunctionASKER
Okay...no error messages but the function still stops on a column that is blank but is coded to only accept a date if completed, column Y or 25. I did have to make some adjustments to the column numbers based on columns that I didn't give you in the original spreadsheet, but all columns are aligned correctly. The only hiccup are these columns that are being checked twice, can be blank but if completed then it must be a specific format. I have attached my current spreadsheet. If you remove the ' from the Workbook vc code, when it saves you will see it automatically stops on column Y/25. Which it shouldn't.
Data-Validation.xlsm
Data-Validation.xlsm
You are right again. There was a logical error in my code. I have ironed it out and you should now be ready to fly.
EXX-131015-Data-Validation.xlsm
EXX-131015-Data-Validation.xlsm
ASKER
Okay...almost there.
In the zip+4 column:
54 · (BB) ZIP+4
o Required, no blanks
o Numbers only
o Must have at least a length of 5
You asked if we had canadian zip codes...no, but when we do the +4 of the zip code for example 15232-1521 the cell bombs because there is a dash '-' in the zip code.
In the zip+4 column:
54 · (BB) ZIP+4
o Required, no blanks
o Numbers only
o Must have at least a length of 5
You asked if we had canadian zip codes...no, but when we do the +4 of the zip code for example 15232-1521 the cell bombs because there is a dash '-' in the zip code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mr Faustulus, this works...this works perfectly. I can not THANK YOU enough. The code and the explanation of everything.
ASKER
Any way to get VB to run on a protected sheet? The spreadsheet we have, we have to lock down the columns, etc. from the users adding columns or removing columns, etc.
Is this a new question? :-)
I like to set the protection using code. That enables setting the property UserInterfaceOnly to True so that code isn't affected by the protection. This property isn't accessible from the keyboard. I prefer this to the alternative which is to lift the protection, write to the sheet, and restore protection. That can be done using code, too.
The best way to formulate your question is to describe the protection you need to set exactly, including whether you use a password or not. Perhaps you like to read up on the Protection Object before you ask.
The answer won't be related to the code you already have. Therefore it doesn't need to be posted.
I like to set the protection using code. That enables setting the property UserInterfaceOnly to True so that code isn't affected by the protection. This property isn't accessible from the keyboard. I prefer this to the alternative which is to lift the protection, write to the sheet, and restore protection. That can be done using code, too.
The best way to formulate your question is to describe the protection you need to set exactly, including whether you use a password or not. Perhaps you like to read up on the Protection Object before you ask.
The answer won't be related to the code you already have. Therefore it doesn't need to be posted.
ASKER
Good point...this is a different question isn't it? You must check out the new question:
:)
https://www.experts-exchange.com/questions/28272526/Using-VBScript-to-Not-Allow-Column-insert-delete.html
:)
https://www.experts-exchange.com/questions/28272526/Using-VBScript-to-Not-Allow-Column-insert-delete.html
It sounds like you are gathering rows of data from people? If so, it may be easier to use a user form.
Would it possible to see the workbook. This will likely be needed for writing the code you need. We could then offer a demo of what we see as the best solution to your whole process, rather than just a fix to the paste issue, which may not be the only issue with 81 columns of data validation.