Excel VBA Validating the values in a column

[dougkale]

I am trying to find a way to check all values entered into a specific column to ensure that all values are a valid mmddyy date.

The column will be of text type, so there is the potential for this column to contain any values. I am not sure if there is a more elegant solution but out of the gate in its most raw form I was thinking I could make sure that:

The length of the value is 6
The value of the 2 left most characters is between 01 and 12
the value of the two center characters {left((right(column,4),2) in sql terms} is between 01 and 31


The right two characters are between say 10 and 30.


Also, on a similar note I'd also like to validate another column to make sure that it contains only values, not formulas.

Any advice?

Thanks in advance.

 

[Joe4]

Here is a little User Defined Function that I whipped up to validate those kind of date entries.

Function DateCheck(entry As String) As Boolean

    Dim mn As Integer
    Dim dy As Integer
    Dim yr As Integer
    
    If Len(entry) <> 6 Then
        DateCheck = False
    Else
        mn = Left(entry, 2)
        dy = Mid(entry, 3, 2)
        yr = Right(entry, 2)
        DateCheck = IsDate(mn & "/" & dy & "/" & yr)
    End If

End Function

You can use this like any other function directly on your worksheet or in VBA code, however you see fit.

 

[dougkale]

How do I point that at a column to process a column in my work sheet. Excuse me, I am very new to Excel VBA

 

[Joe4]

What column would you like to apply it to?
Is it all rows in that column, or are there certain rows to exclude (like maybe you have headers or titles in certain rows)?

Do you want to do this on data entry, so it won't allow them to manually type values that don't meet the criteria, or if this something that you will be running "after the fact" on existing data?

 

[dougkale]

I am actually doing it before save. So they will enter values into a read only template that will require them to save as a different name. When they save the file a great deal of validation and correction will take place. The file being created is fed into a separate process that is very particular of certain things. Column names have to be exact, no blank rows after data, columns sorted on a certain field just to start (I have all of this done).

But for some reason on rare occasions the users enter dollars carried out to 7+ decimal places which crashes the next step of the process.

But the short answer is in checking for formulas and extended decimal places I would be looking only at column D for as far as column D has data and for dates Column E

 

[Joe4]

OK, so it sounds like you will be running this against existing data.
So what would you like to happen exactly when it finds an entry that doesn't meet the requirements?
Do you want it to highlight the cell? populate another cell with some sort of message? pop-up a message box?

 

[dougkale]

I'm sorry, I should have specified that. Highlight the cell then pop up a message. If say the month fails the between 01 and 12 test, display that the date is invalid and must be in the format of mmddyy the same with dd between 01 and 31 and yy between whatever values are deemed appropriate.

 

[Joe4]

OK. Here is VBA code that will check column E for valid dates.
Note I had to make a small amendment to my original DateCheck function, as it seemed to allow months greater than 12 before:

Sub CheckColumnEDates()

    Dim lastRow As Long
    Dim myRow As Long
    
'   Find last row with data in column E
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all rows
    For myRow = 1 To lastRow
        If DateCheck(Cells(myRow, "E")) = False Then
'           Highlight cell
            Cells(myRow, "E").Interior.Color = 65535
'           Pop-up message box
            MsgBox "Cell " & Cells(myRow, "E").Address(0, 0) & " is not a valid date in mmddyy format", vbOKOnly, "DATE ENTRY ERROR!"
        End If
    Next myRow
    
End Sub



Function DateCheck(entry As String) As Boolean

    Dim mn As Integer
    Dim dy As Integer
    Dim yr As Integer
    
    If Len(entry) <> 6 Then
        DateCheck = False
    Else
        mn = Left(entry, 2)
        dy = Mid(entry, 3, 2)
        yr = Right(entry, 2)
        If mn > 12 Then
            DateCheck = False
        Else
            DateCheck = IsDate(mn & "/" & dy & "/" & yr)
        End If
    End If

End Function

So if you paste all the VBA code in a new Module, and then run the Procedure named "CheckColumnEDates", it should do what you want.