Clicking 'Cancel' on an Input box

  • Thread starter Thread starter Big Chris
  • Start date Start date
B

Big Chris

Please can anyone help....

I have a button that a user clicks on which brings up a validation cod
box....a vb input box.
Everything seems to work perfectly except if the user clicks o
'cancel' or the red cross to close the box. In this case it interrupt
the macro revealing a sheet which should be 'veryhidden'!!
Would anyone know what code I need to cause clicks on the Cancel o
Red cross to just close the input box and finish the macro??

I feel like I may just be missing a single line of code or somethin
but I'm new to vb and just can't find it.

The code I am using is below.

Many thanks,

------------------------------------------------------------------------


Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
MyNum = InputBox("Please enter your 8 digit validation code")
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
If CLng(MyNum) = Worksheets("Sheet1").Range("A3").Value Then
Application.OnTime Now, "Deletebutton"
Else

MsgBox "Number is incorrect"

End If

Sheets("Sheet1").Select
Range("A1").Value = MyNum
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("Setup Sheet").Select
End Su
 
I didn't see a reason to unhide sheet1 in your code.

Option Explicit
Sub CommandButton1_Click()
Dim myNum As String

Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled

myNum = InputBox("Please enter your 8 digit validation code")

If myNum = "" Then
Exit Sub
End If

If CLng(myNum) = Worksheets("Sheet1").Range("A3").Value Then
Application.OnTime Now, "Deletebutton"
Else
MsgBox "Number is incorrect"
End If

Worksheets("sheet1").Range("A1").Value = myNum

End Sub


seemed to do what you wanted.
 
The Inputbox function (in Excel) returns False if the Cancel button is
pressed, so you need to add two lines of code as follows:

Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
MyNum = InputBox("Please enter your 8 digit validation code")
Click to expand...

If MyNum said:
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
If CLng(MyNum) = Worksheets("Sheet1").Range("A3").Value Then
Application.OnTime Now, "Deletebutton"
Else

MsgBox "Number is incorrect"

End If

Sheets("Sheet1").Select
Range("A1").Value = MyNum
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("Setup Sheet").Select
Click to expand...

End If
End Sub
Click to expand...


NOTE: There is another Inputbox function in VBA which returns an empty
string if cancel is pressed.
 
Chris

See alterations in-line for one method.

Gord Dibben XL2002

Please can anyone help....

I have a button that a user clicks on which brings up a validation code
box....a vb input box.
Everything seems to work perfectly except if the user clicks on
'cancel' or the red cross to close the box. In this case it interrupts
the macro revealing a sheet which should be 'veryhidden'!!
Would anyone know what code I need to cause clicks on the Cancel or
Red cross to just close the input box and finish the macro??

I feel like I may just be missing a single line of code or something
but I'm new to vb and just can't find it.

The code I am using is below.

Many thanks,

------------------------------------------------------------------------


Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
MyNum = InputBox("Please enter your 8 digit validation code")
Click to expand...
If MyNum = "" Or Not IsNumeric(MyNum) Then
MsgBox "You clicked cancel or did not enter a number"
Exit Sub
End If
 
I think the OP *is* using a VBA InputBox. Don't you have to prefix is
with Excel.Application to qualify it as the Excel version? If not, how
to you explicitly call the VBA version?

Assuming it is the VBA version, here's a Chip Tip:

Q. I'm using an InputBox. How do I determine whether the user presses
Cancel rather than leaving it blank and hitting OK?
A. Use the undocumented StrPtr function:

Dim strInput As String
strInput = InputBox("do something")
If Len(strInput) = 0 Then
If StrPtr(strInput) = 0 Then
MsgBox "The user clicked Cancel"
Else
MsgBox "The user clicked Enter, but typed nothing"
End If
End If

--
 
Back
Top