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")
 

ajlcpa

New Member
Joined
Nov 18, 2011
Messages
15
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,045
Office Version
  1. 2019
Platform
  1. Windows
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,645
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

ajlcpa

New Member
Joined
Nov 18, 2011
Messages
15

ADVERTISEMENT

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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,645
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you figured it out. (y)
But you need to add 3 more pattern:
"##/#/##"
"#/##/####"
"##/#/####"
 

ajlcpa

New Member
Joined
Nov 18, 2011
Messages
15
Glad you figured it out. (y)
But you need to add 3 more pattern:
"##/#/##"
"#/##/####"
"##/#/####"
With your help! Yes, there are more syntaxes (as you noted). I manage the user, so he should fine.
Thank you.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,645
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,972
Messages
5,767,400
Members
425,410
Latest member
SmittyT

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