none
MACRO - Inputbox & MsgBox - Exit Sub if the input value is wrong in format

    Question

  • Hi there,

    I have two inputbox requiring user to input EXACT format of date to do the automation. My codes are as follows:

    strFile = InputBox("Which month of report do you want to generate? (yyyy-mm)(e.g. 2012-06):", , Format(Date, "yyyy-mm"))
        If strFile = "" Then
            MsgBox "No month specified.", vbExclamation
            Exit Sub
        End If
        strFile2 = InputBox("Which date of eximbills reports (IMNE, ICIM, etc.) have you prepared? (yyyy-mm-dd)(e.g. 2012-06-29):", , Format(Date, "yyyy-mm-dd"))
        If strFile2 = "" Then
            MsgBox "No date specified.", vbExclamation
            Exit Sub
        End If

    However, I tried to input "yo" in strFile and the macro still runs. I am afraid my users will input like "2012-June" in strFile or "2012-6-29" in strFile2. Is there any MsgBox showing wrong format and also end the sub if wrong format?

    I mean, I need some code like:

       If strFile = wrong format (yyyy-mm) Then                 OR   If strFile2 = wrong format (yyyy-mm-dd) Then
            MsgBox "Wrong date specified.", vbExclamation
            Exit Sub
        End If

    Thanks in advance!!

    Jacky

    Wednesday, July 04, 2012 9:07 AM

Answers

  • The following will check whether the input can be interpreted as a date.

    If not, the code displays a message, then exits.

    Otherwise, strFile is forced into yyyy-mm format. So even if the user enters 2012-July or 2012/07/04, the result will be 2012-07.

    And strFile2 is forced into yyyy-mm-dd format. So even if the user enters 5 August 2012, the result will be 2012-08-05.

        strFile = InputBox("Which month of report do you want to generate? (yyyy-mm)(e.g. 2012-06):", , _
            Format(Date, "yyyy-mm"))
        If strFile = "" Then
            MsgBox "No month specified.", vbExclamation
            Exit Sub
        ElseIf Not IsDate(strFile) Then
            MsgBox "Invalid date specified.", vbExclamation
            Exit Sub
        End If
        strFile = Format(strFile, "yyyy-mm")
        strFile2 = InputBox("Which date of eximbills reports (IMNE, ICIM, etc.) have you prepared? (yyyy-mm-dd)(e.g. 2012-06-29):", , _
            Format(Date, "yyyy-mm-dd"))
        If strFile2 = "" Then
            MsgBox "No date specified.", vbExclamation
            Exit Sub
        ElseIf Not IsDate(strFile2) Then
            MsgBox "Invalid date specified.", vbExclamation
            Exit Sub
        End If
        strFile2 = Format(strFile2, "yyyy-mm-dd")


    Regards, Hans Vogelaar


    Wednesday, July 04, 2012 9:39 AM

All replies

  • The following will check whether the input can be interpreted as a date.

    If not, the code displays a message, then exits.

    Otherwise, strFile is forced into yyyy-mm format. So even if the user enters 2012-July or 2012/07/04, the result will be 2012-07.

    And strFile2 is forced into yyyy-mm-dd format. So even if the user enters 5 August 2012, the result will be 2012-08-05.

        strFile = InputBox("Which month of report do you want to generate? (yyyy-mm)(e.g. 2012-06):", , _
            Format(Date, "yyyy-mm"))
        If strFile = "" Then
            MsgBox "No month specified.", vbExclamation
            Exit Sub
        ElseIf Not IsDate(strFile) Then
            MsgBox "Invalid date specified.", vbExclamation
            Exit Sub
        End If
        strFile = Format(strFile, "yyyy-mm")
        strFile2 = InputBox("Which date of eximbills reports (IMNE, ICIM, etc.) have you prepared? (yyyy-mm-dd)(e.g. 2012-06-29):", , _
            Format(Date, "yyyy-mm-dd"))
        If strFile2 = "" Then
            MsgBox "No date specified.", vbExclamation
            Exit Sub
        ElseIf Not IsDate(strFile2) Then
            MsgBox "Invalid date specified.", vbExclamation
            Exit Sub
        End If
        strFile2 = Format(strFile2, "yyyy-mm-dd")


    Regards, Hans Vogelaar


    Wednesday, July 04, 2012 9:39 AM
  • Hello Jacky,

    Answer by Hans meets the requirements of your question in that it Exits the sub if incorrect date format. I also point out that the method of validation/manipulation he has used will allow the user to enter the date in other formats like 2012 Jul and it will still return the correct format for strFile.

    However, instead of exiting on just an incorrect format, the code below actually loops and gives the user an opportunity to correct the date format. The message in the InputBox is altered to reflect the error. The code only Exits the sub if the user either Clicks Cancel or clicks OK with a blank entry. (This last part is essential to give the user an "Out" if they so desire by clicking Cancel.)

        Dim strFile As String
        Dim strFile2 As String
        Dim strPrompt As String
        Dim bolInvalid As Boolean
       
        strPrompt = "Which month of report do you want to generate?" _
                    & vbCrLf & "Enter as (yyyy-mm)(e.g. 2012-06):"
       
        Do
            strFile = InputBox(strPrompt, , Format(Date, "yyyy-mm"))
            If strFile = "" Then
                MsgBox "User cancelled or clicked OK with blank entry." _
                        & vbCrLf & "Processing Terminated."
                Exit Sub
            End If
           
            bolInvalid = False
            If Not IsDate(strFile) Then
                strPrompt = "Invalid date. Re-enter" & vbCrLf & _
                            "Which month of report do you want to generate?" _
                    & vbCrLf & "Enter as (yyyy-mm)(e.g. 2012-06):"
                bolInvalid = True
            End If
            strFile = Format(strFile, "yyyy-mm")
        Loop While bolInvalid
       
        strPrompt = "Which date of eximbills reports (IMNE, ICIM, etc.) have you prepared?" _
                    & vbCrLf & "Enter as (yyyy-mm-dd)(e.g. 2012-06-29):"
       
        Do
            strFile2 = InputBox(strPrompt, , Format(Date, "yyyy-mm-dd"))
            If strFile2 = "" Then
                MsgBox "User cancelled or clicked OK with blank entry." _
                        & vbCrLf & "Processing Terminated."
                Exit Sub
            End If
       
            bolInvalid = False
            If Not IsDate(strFile2) Then
                strPrompt = "Invalid date. Re-enter" & vbCrLf & _
                            "Which date of eximbills reports (IMNE, ICIM, etc.) have you prepared?" _
                    & vbCrLf & "Enter as (yyyy-mm-dd)(e.g. 2012-06-29):"
                bolInvalid = True
            End If
            strFile2 = Format(strFile2, "yyyy-mm-dd")
        Loop While bolInvalid


    Regards, OssieMac

    Wednesday, July 04, 2012 12:08 PM
  • Thanks Hans! Thats exactly what I want!

    Thursday, July 05, 2012 1:07 AM
  • Thanks OssieMac !! Thats cool I will try to use it!
    Thursday, July 05, 2012 1:07 AM