Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How do I debug the "Cancel" button from a VBA InputBox?
I'm using VBA to program an InputBox in Excel. It works great if I enter data and hit "OK". But if I hit the "Cancel" button, the VBA Macro stops. I can't figure out how to tell the Macro to "end sub" if the user hits "Cancel".
This is the InputBox function, not the MsgBox.
Below is the code I have for entering a payment date and amount.
Sub pmt()
Dim pmt_date As Date
Dim pmt_amt As Currency
Dim msg As Integer
pmt_date = InputBox("Enter Date", "Payment Date", Date)
pmt_amt = InputBox("Enter Payment Amount", "Payment Amount")
msg = MsgBox("Input Payment: " & pmt_amt & " with payment date: " & pmt_date, vbYesNo, "Input Payment")
If msg = 6 Then
n = 14
Do While Not IsEmpty(Cells(n, 2))
date_loc = date_loc + Cells(n, 2)
n = n + 1
Loop
End If
If msg = 7 Then
Exit Sub
End If
Cells(n, 2) = pmt_date
Cells(n, 3) = pmt_amt
End Sub
5 Answers
- 1 decade agoFavorite Answer
Use "On Error Resume Next" first and then check for the error after the call to the InputBox. If the user clicks "Cancel" on the InputBox, you will get an err.number = 13. You can test for that number explicitly, or just test for any number at all and gracefully exit the subroutine.
On Error Resume Next
pmt_date = InputBox("Enter Date", "Payment Date", Date)
If Err.Number Then
Exit Sub
End If
pmt_amt = InputBox("Enter Payment Amount", "Payment Amount")
If Err.Number Then
Exit Sub
End If
- Richard HLv 71 decade ago
If the user hits the CANCEL button, your input box FUNCTION will return a NULL STRING (intrinsic constant vbNullString, or "" - with nothing between them). You can check for either of those and it will help you detect the CANCEL click.
an example:
Private Sub Login()
Dim strUserName as String
strUserName = inputBox("Please Enter Your Username")
If strUserName = "" Then
'do something with the missing value
Else
'do something with the value given
End If
End Sub
or you can do the following
Private Sub Login()
Dim strUserName as String
strUserName = inputBox("Please Enter Your Username")
If strUserName = vbNullString Then
'do something with the missing value
Else
'do something with the value given
End If
End Sub
I have showed VB-6 code, which is the VBA code for everything until Office 2007. The code may be different if you are using Office 2007, which uses Visual BASIC.NET as the VBA foundation.
- How do you think about the answers? You can sign in to vote the answer.
- Jess WundringLv 41 decade ago
I don't use the InputBox function, but it should work the same way as the MsgBox...see if the returned value = vbCancel.
