Input Box Exit Sub If Not Date or When Cancel is Selected.

ajlcpa

New Member
Joined
Nov 18, 2011
Messages
15
Hi Folks:

I can handle this if I Dim the variable as a string, but I really need to Dim it as a Date. I want to exit the sub if a date is not entered or if Cancel is selected.

Thank you for reading and your help.


VBA Code:
 Dim DateEntry As Date
    
   
   DateEntry = InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection")
 
1. Can you show us your code?
2. What would you do after you assign the date to DateEntry ?

I tried it like this:
run the code below > type 12/04/21 > OK
a message box show up, showing: 12/04/2021

see that 21 become 2021, so DateEntry is a date
note: my regional setting use "day-month-year" not "month-day-year"
VBA Code:
Sub tryDate()
 Dim DateEntry As Date, v As String
    v = Application.InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection", Type:=2)
    If v Like "##/##/##" Then
        DateEntry = CDate(v)
    Else
        Exit Sub
    End If
    MsgBox DateEntry
End Sub


Sure .thank you. Here it is:

VBA Code:
Private Sub ReportStatus()


    Dim a As Integer
    Dim i As Integer
    Dim CopyRange As Range
    Dim DateEntry As Date
 
        Application.ScreenUpdating = False
        
     a = Worksheets("Employee Master").Cells(Rows.Count, 1).End(xlUp).Row
        
   
   
   Worksheets("Status").Activate
   Range("A2:G2000").Clear
   Range("A2:G2000").ClearFormats
   
   DateEntry = Application.InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection")
   
   
  
     For i = 2 To a
       
            If Worksheets("Employee Master").Cells(i, 7).Value >= DateEntry Then  ' DateValue("8/1/2021") Then
            
            With Worksheets("Employee Master")
               Set CopyRange = Application.Union(.Cells(i, 12), .Cells(i, 5), .Cells(i, 7), .Cells(i, 10), .Cells(i, 11), .Cells(i, 4), .Cells(i, 9))
               CopyRange.Copy
               
               End With
            
                        
            Worksheets("Status").Activate
            
            b = Worksheets("Status").Cells(Rows.Count, 1).End(xlUp).Row
            
            Worksheets("Status").Cells(b + 1, 1).Select
            
            ActiveSheet.Paste
            
          
          End If

                Selection.Interior.Color = xlNone
Next
            Columns("A:G").ColumnWidth = 20
            Columns("B:B").ColumnWidth = 40
            Columns("D:D").ColumnWidth = 70
            Selection.Interior.Color = xlNone
            
            
End Sub

I also discovered this ;last night, but haven't tried it.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,
try following update to your inputbox code & see if helps

VBA Code:
   Dim DateEntry    As Variant
   Dim strDefault     As String
  
   Do
   DateEntry = InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection", strDefault)
   'cancel pressed
   If StrPtr(DateEntry) = 0 Then Exit Sub
   strDefault = "Invalid Date Entry"
   Loop Until IsDate(DateEntry)
  
   'coerce string to date
   DateEntry = DateValue(DateEntry)
  
  
   'rest of code

Note: DateEntry variable is declared as variant data type

Dave
 
Last edited:
Upvote 0
Your code should work if you apply my code like this:


VBA Code:
    v = Application.InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection", Type:=2)
    If v Like "##/##/##" Then
        DateEntry = CDate(v)
    Else
        Exit Sub
    End If
  
     For i = 2 To a
       
            If Worksheets("Employee Master").Cells(i, 7).Value >= DateEntry Then  ' DateValue("8/1/2021") Then

but if that doesn't work, try this one:

VBA Code:
    v = Application.InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection", Type:=2)
    If v Like "##/##/##" Then
        DateEntry = CDate(v)
    Else
        Exit Sub
    End If
  
     For i = 2 To a
       
            If CLng(Worksheets("Employee Master").Cells(i, 7).Value) >= CLng(DateEntry) Then  ' DateValue("8/1/2021") Then
 
Upvote 0
Solution
1. Can you show us your code?
2. What would you do after you assign the date to DateEntry ?

I tried it like this:
run the code below > type 12/04/21 > OK
a message box show up, showing: 12/04/2021

see that 21 become 2021, so DateEntry is a date
note: my regional setting use "day-month-year" not "month-day-year"
VBA Code:
Sub tryDate()
 Dim DateEntry As Date, v As String
    v = Application.InputBox("Please enter the oldest start date using this format MM/DD/YY", "Date Selection", Type:=2)
    If v Like "##/##/##" Then
        DateEntry = CDate(v)
    Else
        Exit Sub
    End If
    MsgBox DateEntry
End Sub
This worked! Thank you. I added a bunch of others ways the user may enter a date. Thanks to all!
VBA Code:
 v = Application.InputBox("Please enter the oldest plankton start date using this format MM/DD/YY", "Date Selection", Type:=2)
 
   If v Like "##/##/##" Or v Like "#/#/##" Or v Like "#/##/##" Or v Like "##/##/####" _
    Or v Like "#/#/####" Then
        DateEntry = CDate(v)
    Else
        Exit Sub
    End If
    MsgBox "You selected " & DateEntry
 
Upvote 0
Glad you figured it out. (y)
But you need to add 3 more pattern:
"##/#/##"
"#/##/####"
"##/#/####"
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top