mercredi 19 juillet 2017

VBA can't figure out why Case statement isn't workign correctly

I have this snippet of code in my Worksheet change event, but it's not firing like it should.

If Not Intersect(Target, rng6) Is Nothing Then

        Select Case IsDate(Target.Value)
        Case False
        Target.NumberFormat = "m/yyyy"
        Target = Empty
        Case True

            If Format(Target.Value, "m/yyyy") < Format(Now, "m/yyyy") Then
            MsgBox "Must not be less than" & vbLf & "current month & year !", vbExclamation, "Warning.."
            Target.Value = Format(Now, "m/yyyy")
            Else
            Target.NumberFormat = "m/yyyy"
            MsgBox Format(Target.Value, "m/yyyy") & "  " & Format(Now, "m/yyyy")
            End If

        End Select

    End If

The FALSE section is working properly, but when it gets to TRUE and runs through the if statement, it's returning only one or the other.

For example, I test entering in 01/2017 and it returns the first part of the IF, but when I enter in 10/2017 it returns the first part ALSO and converts it to today's M/YYYY, instead of the second part which it should because the current m/yyyy is greater than today's m/yyy.

Can anyone identify what the error is here?? I'm sure it's simple and the solution is right there/

Thanks

Aucun commentaire:

Enregistrer un commentaire