Need cancel code when input box open

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,147
Hello Board Members,

I have below code with me. I'm using one command button. After click on 'button', message box appearing and asking user to input date. I want in this code, when this input box appear and suppose user click on cancel or close button, it shall give msg - "You have not entered a date" with error style msg window.

The part which i want to edit highlighted in bold. Pls suggest..

VBA Code:
Sub Button1_Click()

Dim strDate As String
Dim acceptDate As Integer
    
Do
    Do
        strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "Date", Format(Date - 1, "mm/dd/yyyy"))
        
       [B] If Not IsDate(strDate) Then MsgBox "Please enter a date!", vbCritical
    
    Loop Until IsDate(strDate)[/B]
   
    strDate = Format(CDate(strDate), "mm/dd/yyyy")
    acceptDate = MsgBox("The date you entered is " & strDate & vbNewLine & "Accept this date?", vbYesNo)

Loop Until acceptDate = vbYes

Sheets("Sheet1").Range("A3") = strDate

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
320
Try this
VBA Code:
Sub Button1_Click()
Dim strDate As String
Dim acceptDate As Integer
    
Do
    Do
        strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "Date", Format(Date - 1, "mm/dd/yyyy"))
        If strDate = "" Then
            MsgBox "You have not entered a date", vbCritical
        ElseIf Not IsDate(strDate) Then
            MsgBox "Please enter a date!", vbCritical
        End If
    Loop Until IsDate(strDate)
    
    strDate = Format(CDate(strDate), "mm/dd/yyyy")
    acceptDate = MsgBox("The date you entered is " & strDate & vbNewLine & "Accept this date?", vbYesNo)

Loop Until acceptDate = vbYes
Sheets("Sheet1").Range("A3") = strDate

End Sub
[code]
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,965
Office Version
  1. 2016
Platform
  1. Windows
Here is a generalized structure you can use for the InputBox that will allow you to react to the user clicking the Cancel button or clicking Enter without filling in anything as well as when the user enters actual data...

Code:
Dim Answer As String
 '....
 '....
 Answer = InputBox("Tell me something")
 If StrPtr(Answer) = 0 Then
   MsgBox "The user clicked Cancel, so we will exit the subroutine now."
   Exit Sub
 ElseIf Len(Answer) = 0 Then
   MsgBox "The user clicked OK without entering anything in the InputBox!"
 Else
   MsgBox "The user entered the following..." & vbLf & vbLf & Answer
 End If
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,147
Hi Phuoc, Thanks for your time. But what i look is, if i clicked on cancel or close on msg box, it will show me msg that 'U hv not entered any details' and close the entire loop. And hence I followed what Rick Sir suggested me.

Hello Rick Sir, after very long time.. :)

Sir I modified the statement like this..
VBA Code:
Do
        strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "Date", Format(Date - 1, "mm/dd/yyyy"))
        
        If Not IsDate(strDate) Then MsgBox "You have not entered a date!"
        Exit Sub

Hence Rick Sir and Phuoc for your valuable time and reply. 👏🤝
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,147

ADVERTISEMENT

Hello again here.. I dont know but I'm getting error on this spot..
VBA Code:
strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "Date", Format([SIZE=6][COLOR=rgb(184, 49, 47)]Date[/COLOR][/SIZE] - 1, "mm/dd/yyyy"))
Error is - Compile error: Cant find project or library.. Little bit worried. Can anyone of you please suggest..
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,147
dear members, pls guide on this.. i think this about library not found.. and i'm not aware how to get that in order to resolve this.. very close actually.. hence report.. thanks..
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

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