VBA: Input Box, on Error another chance, on cancel exit

[jamescooper]

Sub Clean_Data()


Dim myValue As Variant
Dim myValue2 As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim FP As String, FN As String


myValue = InputBox("Enter the railway period, e.g. 1804")
myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")

I am trying to prevent the code from bringing up the error message on the following Input box above.

In the first if the 'myvalue' is not in a list how would I make the user try again? Also where would I put this list, could I put it in the code?

2nd, on myvalue2 if it is an error how do I make them enter again until correct.

3rd if they click "Cancel" how does it exit the macro?

Many thanks in advance.

 

[mart37]

Dim Found As Boolean
Dim z As Byte
Dim Items() As String
Items = Split("1804,1875,1905,1955,2004", ",")
Found = False
Do
    myValue = InputBox("Enter the railway period, e.g. 1804")
    If StrPtr(myValue) = 0 Then Exit Sub
    For z = LBound(Items) To UBound(Items)
        If myValue = Items(z) Then Found = True
    Next
Loop While Not Found
Do
    myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")
    If StrPtr(myValue2) = 0 Then Exit Sub
Loop While Not IsDate(myValue2)

 

[jamescooper]

Dim Found As Boolean
Dim z As Byte
Dim Items() As String
Items = Split("1804,1875,1905,1955,2004", ",")
Found = False
Do
    myValue = InputBox("Enter the railway period, e.g. 1804")
    If StrPtr(myValue) = 0 Then Exit Sub
    For z = LBound(Items) To UBound(Items)
        If myValue = Items(z) Then Found = True
    Next
Loop While Not Found
Do
    myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")
    If StrPtr(myValue2) = 0 Then Exit Sub
Loop While Not IsDate(myValue2)

Thanks that is great, the myvalue bit works, the bit that doesn't work if I enter a date where there isn't a file in the folder in the myvalue2 bit. Any ideas? Basically not looping?

Thanks.

 

[mart37]

You can check of a file exists with:

Dirpath = "D:\"
If Dir(Dirpath & myValue2 & ".xlsx")="" then ... (not exists)

 

[jamescooper]

You can check of a file exists with:

Dirpath = "D:\"
If Dir(Dirpath & myValue2 & ".xlsx")="" then ... (not exists)

Thanks, so how would it work below?

Thanks.

Dirpath = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Data\" & myValue & "\Raw\"
If Dir(Dirpath & myValue2 & ".xlsb")="" 'then ... (not exists)


    myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")
    If StrPtr(myValue2) = 0 Then Exit Sub
Loop While Not IsDate(myValue2)

 

[mart37]

Dim NoFile As Boolean
NoFiles = False
Do
    myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")
    If StrPtr(myValue2) = 0 Then Exit Sub
    dirpath = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Data\" & myValue & "\Raw\"
    If Dir(Dirpath & myValue2 & ".xlsb") = "" Then NoFile = True
Loop While Not IsDate(myValue2) Or NoFile

 

[jamescooper]

Dim NoFile As Boolean
NoFiles = False
Do
    myValue2 = InputBox("Enter the date you wish to clean the data in format yyyy-mm-dd e.g. 2017-06-28")
    If StrPtr(myValue2) = 0 Then Exit Sub
    dirpath = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Data\" & myValue & "\Raw\"
    If Dir(Dirpath & myValue2 & ".xlsb") = "" Then NoFile = True
Loop While Not IsDate(myValue2) Or NoFile

Thanks that is great.