VBA - InputBox with four different eventualities accounted for

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
579
Office Version
  1. 2010
Platform
  1. Windows
Have been looking at existing threads on this as well as on other forums, I thought for sure this would work...


VBA Code:
    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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,483
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try following & see if helps you

VBA Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
579
Office Version
  1. 2010
Platform
  1. Windows
Hi,
try following & see if helps you

VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,366
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top