VBA Loop

[dumbitdown]

Afternoon all,

Looking for a bit of help with looping...

Basically I've created a macro that gets the user to enter data via input boxes and validates the info they have entered, when the format of the info is incorrect I have an error message pop up, when ok is clicked on this error message I want it to loop back round to get them to re-enter the data in the correct format.

I've extracted a bit of the macro to demonstrate (below), how do I make it loop back round to get them to try and enter the data correctly?

Dim strDate As String
strDate = InputBox("Target Date dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
If IsDate(strDate) Then
strDate = Format(CDate(strDate), "dd/mm/yyyy")
MsgBox strDate
Else
MsgBox "Wrong date format"

Any help you can give would be brilliant!

 

[VoG]

Perhaps

Dim strDate As String
Do Until IsDate(strDate)
    strDate = InputBox("Target Date dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
    If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "dd/mm/yyyy")
        MsgBox strDate
    Else
        MsgBox "Wrong date format"
    End If
Loop

 

[dumbitdown]

Tried it, works, brilliant! Who knew it would be so simple...

 

[VoG]

This works perfectly for me

Sub atest()
Dim strDate As String
Do Until IsDate(strDate)
    strDate = InputBox("Target Date dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
    If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "dd/mm/yyyy")
        MsgBox strDate
    Else
        MsgBox "Wrong date format"
    End If
Loop
End Sub

 

[dumbitdown]

Thanks for your help, I have just noticed an issue with my own code, I want it to insert the date into the active cell however it's validating the data and making the date pop up in a message box - could you help me make it validate the date and insert it into the active cell? (hope that makes sense!) Thanks

 

[VoG]

Try

Sub atest()
Dim strDate As String
Do Until IsDate(strDate)
    strDate = InputBox("Target Date dd/mm/yyyy", "User date", Format(Now(), "dd/mm/yyyy"))
    If IsDate(strDate) Then
        ActiveCell.Value = DateValue(strDate)
    Else
        MsgBox "Wrong date format"
    End If
Loop
End Sub

 

[dumbitdown]

Brilliant, thanks!!!