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.
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
You are right that this can be done using VBA.
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.
0
FaustulusCommented:
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.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

FaustulusCommented:
@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?
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
FaustulusCommented:
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. :-)
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
0
FaustulusCommented:
Please pardon the upcoming delay. I'm travelling and expect to  be able to look into your workbook toward the end of this week.
0
FaustulusCommented:
OK. Can we do some more load sharing? The attached file needs checking and testing.
First, let me show you around.
The workbook has code in it. Therefore it has an xlsm extension. You can get at the code by opening the VB Editor window. Press Alt+F11 or, from the Developer tab (look up how to add this tab to your ribbon if you don't have it) click the 'Visual Basic' button on the extreme left. In the VBE you will see the Project Explorer window on the left. Expand your project (identified by file name) and double click on 'ThisWorkbook'. You will see this code,
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'    Cancel = Not EntryValidation
End Sub

Open in new window

This procedure will run when you press the Save button. But it is disabled. Observe the apostroph at the beginning of line 2, before the word Cancel. If you remove it the procedure will be hot. I disabled it because I wanted to save the workbook without correcting all the mistakes.
Double-Click on the module 'ValidationMan'. You will see the actual code that does all the work. You can place the cursor anywhere within the function 'EntryValidation', that is between this word and the 'End Function' statement much further down. Then press F5 to run the code or click on Run and Run Sub to the same effect. This action calls the same procedure as the BeforeSave event procedure. Please wait a little before you try it. This should come first.

Near the top of the function 'EntryValidation' you will see the code line
Set Ws = ThisWorkbook.Worksheets("Sheet1")
Here you can change the sheet (tab) name, if you later wish to change it.
Above the procedure, in the Enum Nws you see the enumeration NwsFirstDataRow = 2. You can change that value to 3 or 4 or whatever if you increase the size of your header in the future. Right now, there is one header row and row 2 is the first row containing data. No checking will be done above that row (perhaps useful for testing, at times).

Now for the checking.
I have copied your instructions to Sheet2 and added the row numbers in column A. You may like to check these numbers, because if there was any error in your list, it was carried into mine. And if there was no error in your list, I may have made some of my own. Excel addresses its columns by number, and if I did the conversion wrong the wrong column will be checked.
While you are at it please take note of the additions I have made and the remarks I have added in column H. My additions are all in blue font.

Now please return to the code. You will see many lines like this one,
Case 1                      ' column A
      If Not CheckCategory(Cell) Then Exit Function
The translation of this is to say that if the program looks at a cell in column 1 (=A) it should run the function CheckCategory, and if an error is found there exit the function, which would have the effect of selecting the cell and telling the user to fix it. Later, you can (and should) create wrong and correct entries in all the columns so that you can see how the program responds.
Right now it is important to check the columns. A = 1. No problem. But further down you come to more complicated code like,
Case 9, 15, 20, 21, 33, 34, 76       ' columns I, O, T, U, AG, AH, BZ
       If Not EntryIsDate(Cell) Then Exit Function
and
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
Both lines apply the same rule to many columns, like columns 9, 15, 20 etc. all of which require a date, no blank. The second group also requires a date but permits a blank. Please check that the column 9, for example, is indeed column I and that your instruction for column I was indeed that a Date is required (no blank). It should be easy to confuse type one and type two and, as the evening wore on, it may have been entirely possible for me to add a column to the wrong test all together. You can move numbers from one 'Case' to another if you find such errors.

With that out of the way you can start testing. I removed all your data validation from the sheet's first row so that you can enter whatever you want. The program will check from column A to CD and stop when it finds an error. For example, for the Category, I entered nothing, then something, then a correct entry in the wrong case and finally a correct entry. That column works perfectly. But after about column I or K there were many repetitions and not all testing was done as thoroughly as I would have preferred.

In fact, even a single complete row was never tested. And that is why I don't know if the macro actually will test a full row. Nor do I know if it will actually take a second and third row as intended. I just don't have the data to test.
The macro determines whether or not to start testing a row by looking at it. If there are any entries in it it will validate. That could be one lone entry in column DX. I have considered checking a row only of column A has a value. Please consider and advise. We can change that.

Once an entry is found every column will be checked. But in my test there are only 77 columns in the region, meaning there are no 81 columns with headers. I didn't bother about it, but you should check to make sure that all columns are, in fact checked. Just create an error in the very last one and see if it is found. If there is a problem of this kind please provide a workbook with data in it.
EXX-131003-Data-Validation.xlsm
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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
0
FaustulusCommented:
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.
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 Function

Open in new window

I hope this will not be the case but if you find any more errors please advise the exact error message you are getting.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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
0
FaustulusCommented:
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
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
FaustulusCommented:
OK, let's try this again.
The new function IsLongNumberWithDash should be able to handle numbers with multiple dashes, too, like telephone numbers. But only the first element will be checked for number of digits.
EXX-131015-Data-Validation.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee R Liddick JrReporting AnalystAuthor Commented:
Mr Faustulus, this works...this works perfectly.  I can not THANK YOU enough.  The code and the explanation of everything.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
FaustulusCommented:
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.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Good point...this is a different question isn't it?  You must check out the new question:
:)
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28272526.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.