Inputbox Cancel

DBaker7777

New Member
Joined
Feb 3, 2009
Messages
37
I have the following code which works great if the user cancels the first inputbox, but I can't seem to get it to work for the enddate if the user selects cancel but enters data into the startdate. Is there a way to write this so if the user selects cancel on either inputbox it exits the sub?

VBA Code:
Sub reportgenerationbasedondates()
Dim lastrow As LongPtr, i As LongPtr, erow As LongPtr
Dim sheetdate As Date, startdate As Date, enddate As Date
Dim response As Variant
response = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date")
Select Case StrPtr(response)
Case 0
Exit Sub

Case Else

lastrow = Worksheets("Entries").UsedRange.Rows.Count

startdate = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date")
enddate = InputBox("Enter the end date as mm-dd-yyyy", "Enter End Date")


For i = 2 To lastrow
    sheetdate = Cells(i, 1)
        If sheetdate >= startdate And sheetdate <= enddate Then
               erow = Worksheets("Sheet2").UsedRange.Rows.Count + 1
        Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheets("Sheet2").Cells(erow, 1)
        End If
        
Next i




End Select

End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
why you are asking for date three times? anyway, this should work (you have it in first inputbox at the begining of the code):

VBA Code:
startdate = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date")
If StrPtr(startdate) = 0 Then Exit Sub
enddate = InputBox("Enter the end date as mm-dd-yyyy", "Enter End Date")
If StrPtr(enddate) = 0 Then Exit Sub
 

DBaker7777

New Member
Joined
Feb 3, 2009
Messages
37
why you are asking for date three times? anyway, this should work (you have it in first inputbox at the begining of the code):

VBA Code:
startdate = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date")
If StrPtr(startdate) = 0 Then Exit Sub
enddate = InputBox("Enter the end date as mm-dd-yyyy", "Enter End Date")
If StrPtr(enddate) = 0 Then Exit Sub
I am getting Run Time Error '13' Type mismatch when I hit the cancel button.

VBA Code:
Sub reportgenerationbasedondates()
Dim lastrow As LongPtr, i As LongPtr, erow As LongPtr
Dim sheetdate As Date, startdate As Date, enddate As Date

lastrow = Worksheets("Entries").UsedRange.Rows.Count

startdate = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date") 'This line is highlighted on the Run Time Error '13' type mistmatch when I hit cancel
If StrPtr(startdate) = 0 Then Exit Sub

enddate = InputBox("Enter the end date as mm-dd-yyyy", "Enter End Date")
If StrPtr(enddate) = 0 Then Exit Sub



For i = 2 To lastrow
    sheetdate = Cells(i, 1)
        If sheetdate >= startdate And sheetdate <= enddate Then
               erow = Worksheets("Sheet2").UsedRange.Rows.Count + 1
        Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheets("Sheet2").Cells(erow, 1)
        End If
        
Next i

End Sub
 

DBaker7777

New Member
Joined
Feb 3, 2009
Messages
37
Ok this seemed to work from doing more research. I added On Error Resume Next above the inputbox and changed the StrPtr to CDate. When I pressed cancel on either box it did exit the sub without a run time error.

VBA Code:
Sub reportgenerationbasedondates()
Dim lastrow As LongPtr, i As LongPtr, erow As LongPtr
Dim sheetdate As Date, startdate As Date, enddate As Date


lastrow = Worksheets("Entries").UsedRange.Rows.Count
On Error Resume Next 'Added this to make it exit
startdate = InputBox("Enter start date as mm-dd-yyyy", "Enter Start Date")
  If CDate(startdate) = 0 Then
        MsgBox "You pressed cancel!  Now exiting"
        Exit Sub
  End If
    
enddate = InputBox("Enter the end date as mm-dd-yyyy", "Enter End Date")
If StrPtr(enddate) = 0 Then Exit Sub
 If CDate(enddate) = 0 Then
        MsgBox "You pressed cancel!  Now exiting"
        Exit Sub
 End If


For i = 2 To lastrow
    sheetdate = Cells(i, 1)
        If sheetdate >= startdate And sheetdate <= enddate Then
               erow = Worksheets("Sheet2").UsedRange.Rows.Count + 1
        Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheets("Sheet2").Cells(erow, 1)
        End If
        
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,604
Messages
5,625,755
Members
416,133
Latest member
ToseSenpai

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