Using Format() function in an if statement [VBA]

[328mike]

Hey all,

Is it possible to use the format function in a VBA IF statement?

This is what I'm working with:

'ETC Billing Month Column
Dim DataMonth
Dim DataMonthFormatted
Dim ETCMonth
DataMonth = InputBox("Please enter a data month. (Mm/01/Yyyy)")

If Format(DataMonth) <> Format(DataMonth, "Mm/Dd/Yyyy") Then
MsgBox ("Invalid date format! Please try again. (Mm/01/Yyyy)")
DataMonth = InputBox("Please enter a data month. (Mm/01/Yyyy)")

Else
ETCMonth = Month(DataMonth)
DataMonthFormatted = Format(DataMonth, "Yyyy-Mm")
Dim j As Long
For j = 1 To DataRange
Cells(1 + j, 1).Formula = DataMonthFormatted
If Month(Cells(1 + j, 5)) = ETCMonth Then
Cells(1 + j, 10).Value = "Y"
End If
Next j
End If

I basically want a prompt to re-enter the date if someone makes a typo until it is in the correct format.

TIA!

 

[Smitty]

Welcome to the Board!

I'd use the Date/Time Picker control instead, that way you don't need to worry about it. Martin Green has a good tutorial here.

HTH,