Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Box Cancel | Excel Discussion (Misc queries) | |||
Exit Code Upon Cancel On Type 8 Input Box | Excel Discussion (Misc queries) | |||
Input Box - CANCEL | Excel Worksheet Functions | |||
cancel input | Excel Discussion (Misc queries) | |||
input box cancel button | Excel Programming |