VBA - InputBox with four different eventualities accounted for

[RockandGrohl]

Have been looking at existing threads on this as well as on other forums, I thought for sure this would work...

    Dim wcex As Date
    wcex = InputBox("Choose a Week Commencing Data in 'dd/mm/yyyy' format only", "Enter Week Commencing Data to Export")
    wcex = Format(wcex, "dd/mm/yyyy")
    If StrPtr(wcex) = 0 Then
        MsgBox ("Cancelled - Ending Process")
        Exit Sub
    ElseIf Len(wcex) = 0 Then
        MsgBox ("No input - Ending Process")
        Exit Sub
    ElseIf Not WorksheetFunction.Weekday(wcex) = 2 Then
        MsgBox ("The chosen date of " & wcex & " is not a Monday, try harder")
        Exit Sub
    Else
        MsgBox ("The week " & wcex & " will now export to the Studio Folder")
    End If

So there's four eventualities. The user is prompted to enter a week commencing date.

1) User cancels - end the sub
2) User enters blank - end the sub
3) User enters date that is not a Monday - end the sub
4) User enters a correct date - continue the sub


The first thing that happens is the user will enter a date, say "09/11/2020" - it will make sure this is read as "09 Nov 2020" and not "11 Sep 2020"


What's happening so far is that if I dim wcex as String, if the string is blank or incorrect, it alerts them as it should, but it continually claims that 09/11/2020 is not a Monday, even though it is.

So if I dim wcex as a Date, then the Monday check works perfectly, but if I enter a blank date or cancel, I get a "Type mismatch"


Any ideas on how I can get all four eventualities input? What about a 5th one, if they enter text? Thanks!

 

[dmt32]

Hi,
try following & see if helps you

Sub RockAnd()
    Dim wcex        As Variant
    Dim IsValidDate As Boolean
    
    Do
        wcex = InputBox("Choose a Week Commencing Data in" & Chr(10) & _
                        "dd/mm/yyyy' format only", _
                        "Enter Week Commencing Data To Export")
        
        If StrPtr(wcex) = 0 Then MsgBox ("Cancelled - Ending Process"): Exit Sub
        
        If IsDate(wcex) Then
            wcex = DateValue(wcex)
            IsValidDate = WorksheetFunction.Weekday(wcex) = vbMonday
            If Not IsValidDate Then
                MsgBox "The chosen Date of " & wcex & " Is Not a Monday, try harder", 48, "Invalid Date"
            Else
                MsgBox "The week " & wcex & " will now export To the Studio Folder", 64, "Valid Date"
            End If
        End If
    Loop Until IsValidDate
End Sub

Dave

 

[RockandGrohl]

Hi,
try following & see if helps you

Sub RockAnd()
    Dim wcex        As Variant
    Dim IsValidDate As Boolean
   
    Do
        wcex = InputBox("Choose a Week Commencing Data in" & Chr(10) & _
                        "dd/mm/yyyy' format only", _
                        "Enter Week Commencing Data To Export")
       
        If StrPtr(wcex) = 0 Then MsgBox ("Cancelled - Ending Process"): Exit Sub
       
        If IsDate(wcex) Then
            wcex = DateValue(wcex)
            IsValidDate = WorksheetFunction.Weekday(wcex) = vbMonday
            If Not IsValidDate Then
                MsgBox "The chosen Date of " & wcex & " Is Not a Monday, try harder", 48, "Invalid Date"
            Else
                MsgBox "The week " & wcex & " will now export To the Studio Folder", 64, "Valid Date"
            End If
        End If
    Loop Until IsValidDate
End Sub

Dave

Dave, that's awesome.

First time I've seen Do used like that, usually I write "Do until" - what's the difference with your version?

 

[dmt32]

Hi,
just another way of writing the loop - you can read more in helpfile: Do...Loop statement (VBA)

glad solution helped.

Dave