Clicking 'Cancel' on an Input box
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Clicking 'Cancel' on an Input box
 

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.

Big Chris wrote:

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

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

Clicking 'Cancel' on an Input box
 

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

If MyNum < False then

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 If

End Sub


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

--
Michael Hopwood (Phobos)


"Big Chris" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Clicking 'Cancel' on an Input box
 

Chris

See alterations in-line for one method.

Gord Dibben XL2002

On Thu, 27 Nov 2003 12:59:01 -0600, Big Chris
wrote:


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")
If MyNum = "" Or Not IsNumeric(MyNum) Then
MsgBox "You clicked cancel or did not enter a number"
Exit Sub
End If
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 Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Clicking 'Cancel' on an Input box
 

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

--

"Michael Hopwood" wrote in message ...
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")

If MyNum < False then

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 If

End Sub


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

--
Michael Hopwood (Phobos)


"Big Chris" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/