Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
With the OK and CANCEL options, I tried all the commented out exit codes for the Input Box and all produce an error on this line:
sNum = InputBox("Stock Number Required.", "Quantidade Retirada") Otherwise the box and code work okay. Howard myIB2: sNum = InputBox("Stock Number Required.", "Quantidade Retirada") ' If sNum = vbNullString Then ' MsgBox ("User canceled!") ' End If 'If sNum = vbNullString Then Exit Sub 'If sNum = "" Then Exit Sub 'If sNum = 0 Then Exit Sub If sNum rngFnd.Offset(, 6) Then MsgBox "Stock Actual is: " & rngFnd.Offset(, 6) & _ " You are requesting: " & sNum & vbCr & vbCr & _ " Stock Minimo is: " & rngFnd.Offset(, 5) Sheets("Saídas").Range("F10").ClearContents GoTo myIB2 'Exit Sub Else '/ Continue code Sheets("Saídas").Range("F10") = sNum rngFnd.Offset(, 6) = rngFnd.Offset(, 6) - sNum rngFnd.Offset(, 6).Copy Sheets("Saídas").Range("J10") End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
This works all day long...
Sub test() Dim sNum$ sNum = InputBox("Stock Number Required.", "Quantidade Retirada") If sNum = "" Then MsgBox "Nothing entered" End Sub ...whether you click Ok, Cancel, X or press the Esc key because VB's InputBox input control is a TextBox. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Hi Garry,
Still cannot get it to work. Here is the whole code, perhaps that has something to do with it. Look for the sNum = InputBox is where I need the error check. The first InputBox you can escape from but not sNum??? Thanks. Howard Private Sub botão_procurar_Click() Dim LRow As Long Dim aRng As Range, rngFnd As Range Dim myFnd As String Dim sNum$ [F6,F8,F10,J10].ClearContents myIB1: myFnd = InputBox("Por favor, introduza o código do artigo que deseja retirar.", "Retirar Material") If myFnd = "" Then Exit Sub ElseIf IsNumeric(myFnd) Then myFnd = Val(myFnd) '/ converts a "text" number to a value Else '/ is text and that is okay End If With Sheets("Registos Globais") LRow = Sheets("Registos Globais").Cells(Rows.Count, "A").End(xlUp).Row Set rngFnd = Sheets("Registos Globais").Range("A2:A" & LRow).Find(What:=myFnd, _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not rngFnd Is Nothing Then rngFnd.Copy Sheets("Saídas").Range("F6") rngFnd.Offset(, 2).Copy Sheets("Saídas").Range("F8") rngFnd.Offset(, 7).Copy Sheets("Saídas").Range("f12") rngFnd.Offset(, 6).Copy Sheets("Saídas").Range("J10") '/msgbox show amount of rngFnd MsgBox "You can pick up to: " & rngFnd.Offset(, 6) & " Stocks for Código I: " _ & vbCr & " " & myFnd _ & vbCr & " " & Range("f8") myIB2: sNum = InputBox("Pick no more than " & vbCr & _ " " & rngFnd.Offset(, 6) & " Stocks" & vbCr & "for Código I: " & myFnd & _ " Quantidade Retirada in Cell F10") '/ ****** ' Ok with no entry or Cancel here '/****** If sNum rngFnd.Offset(, 6) Then MsgBox "Stock Actual is: " & rngFnd.Offset(, 6) & _ " You are requesting: " & sNum & vbCr & vbCr & _ " Stock Minimo is: " & rngFnd.Offset(, 5) Sheets("Saídas").Range("F10").ClearContents GoTo myIB2 'Exit Sub Else '/ Continue code Sheets("Saídas").Range("F10") = sNum rngFnd.Offset(, 6) = rngFnd.Offset(, 6) - sNum rngFnd.Offset(, 6).Copy Sheets("Saídas").Range("J10") End If End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Hi Howard,
Am Wed, 22 Oct 2014 16:43:21 -0700 (PDT) schrieb L. Howard: With the OK and CANCEL options, I tried all the commented out exit codes for the Input Box and all produce an error on this line: sNum = InputBox("Stock Number Required.", "Quantidade Retirada") try the Application.InputBox if the user presses "OK" with no entry he gets an error message in the sheet. If he presses "Cancel" he gets a MsgBox: sNum = Application.InputBox("Stock Number Required.", _ "Quantidade Retirada", Type:=1) If sNum = False Then MsgBox "You canceled" Exit Sub End If Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Thanks for the nudge, Claus.
Got it to work using this. Regards, Howard sNum = InputBox("Pick no more than " & vbCr & _ " " & rngFnd.Offset(, 6) & " Stocks" & vbCr & "for Código I: " & myFnd & _ " Quantidade Retirada in Cell F10") If sNum = "" Then [F6,F8,F10,J10].ClearContents Exit Sub ElseIf sNum = False Then [F6,F8,F10,J10].ClearContents 'MsgBox "You canceled" Exit Sub End If |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Thanks for the nudge, Claus.
Got it to work using this. Regards, Howard sNum = InputBox("Pick no more than " & vbCr & _ " " & rngFnd.Offset(, 6) & " Stocks" & vbCr & "for Código I: " & myFnd & _ " Quantidade Retirada in Cell F10") If sNum = "" Then [F6,F8,F10,J10].ClearContents Exit Sub ElseIf sNum = False Then [F6,F8,F10,J10].ClearContents 'MsgBox "You canceled" Exit Sub End If This is still using VB's InputBox and so the 'EldeIf' will raise an "Invalid procedure call or argument" error! Otherwise, 'If sNum = ""' means user cancelled the InputBox. This would be the case even if text was entered but the OK button not clicked! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
On Thursday, October 23, 2014 12:33:21 AM UTC-7, GS wrote:
Thanks for the nudge, Claus. Got it to work using this. Regards, Howard sNum = InputBox("Pick no more than " & vbCr & _ " " & rngFnd.Offset(, 6) & " Stocks" & vbCr & "for Código I: " & myFnd & _ " Quantidade Retirada in Cell F10") If sNum = "" Then [F6,F8,F10,J10].ClearContents Exit Sub ElseIf sNum = False Then [F6,F8,F10,J10].ClearContents 'MsgBox "You canceled" Exit Sub End If This is still using VB's InputBox and so the 'EldeIf' will raise an "Invalid procedure call or argument" error! Otherwise, 'If sNum = ""' means user cancelled the InputBox. This would be the case even if text was entered but the OK button not clicked! -- Garry The tests I did was to click the red "X", click OK (with no entry) and click Cancel (with no entry). I got no errors. When entries are made they work also. So do I have more error checking code than needed? Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
I'd probably go with something like...
Dim sNum$, sMsgs$ smsg = "Pick no more than " & vbCr _ & Space(10) & rngFnd.Offset(, 6) & " Stocks" & vbCr _ & "for Código I: " & myFnd & " Quantidade Retirada in Cell F10" sNum = InputBox(smsg, "Quantidade Retirada") If sNum = "" Then [F6,F8,F10,J10].ClearContents 'MsgBox "You canceled" Exit Sub End If -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
On Thursday, October 23, 2014 1:05:18 AM UTC-7, GS wrote:
I'd probably go with something like... Dim sNum$, sMsgs$ smsg = "Pick no more than " & vbCr _ & Space(10) & rngFnd.Offset(, 6) & " Stocks" & vbCr _ & "for Código I: " & myFnd & " Quantidade Retirada in Cell F10" sNum = InputBox(smsg, "Quantidade Retirada") If sNum = "" Then [F6,F8,F10,J10].ClearContents 'MsgBox "You canceled" Exit Sub End If -- Garry I'll give it a go. Thanks Garry. Howard |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Oops.., there's a typo...
Sub test() Dim sNum$, sMsg$ sMsg = "Pick no more than " & vbCr _ & Space(10) & rngFnd.Offset(, 6) & " Stocks" & vbCr _ & "for Código I: " & myFnd & " Quantidade Retirada in Cell F10" sNum = InputBox(sMsg, "Quantidade Retirada") If sNum = "" Then MsgBox "Nothing entered" End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
On Thursday, October 23, 2014 3:18:05 AM UTC-7, GS wrote:
Oops.., there's a typo... Sub test() Dim sNum$, sMsg$ sMsg = "Pick no more than " & vbCr _ & Space(10) & rngFnd.Offset(, 6) & " Stocks" & vbCr _ & "for Código I: " & myFnd & " Quantidade Retirada in Cell F10" sNum = InputBox(sMsg, "Quantidade Retirada") If sNum = "" Then MsgBox "Nothing entered" End Sub -- Garry Yes, I caught the typo, and it works well with "cleaner" coding. Thanks again. Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
You're welcome, ..as always!
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Hi friends
You have solved this, let me just share an old trick: Sub test() Dim S As String S = InputBox("Enter anything or nothing:") If StrPtr(S) = 0 Then MsgBox "You cancelled" Else MsgBox "You entered:" & S End If End Sub Opposed to other variables, VBA will not know how much memory a String will need. So what it does when a string is used is to save it "somewhere/anywhere available" and put a pointer, the address to the text, i variable S. When you cancel, the string is not used and the stringpointer, StrPtr, remains zero. Beste wishes Harald "L. Howard" skrev i melding ... With the OK and CANCEL options, I tried all the commented out exit codes for the Input Box and all produce an error on this line: sNum = InputBox("Stock Number Required.", "Quantidade Retirada") Otherwise the box and code work okay. Howard |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
Opposed to other variables, VBA will not know how much memory a
String will need. So what it does when a string is used is to save it "somewhere/anywhere available" and put a pointer, the address to the text, i variable S. When you cancel, the string is not used and the stringpointer, StrPtr, remains zero. Harald, Thanks for sharing an 'old trick'; these are always welcome! Unfortunately, it doesn't work when users "enter nothing" because doing so (leave empty, hit Enter key) returns an empty string (or vbNullString) and so StrPtr is not zero... Sub test() Dim S$, sMsg$ S = InputBox("Enter anything or nothing:") If StrPtr(S) = 0 Then sMsg = "You cancelled" Else sMsg = "StrPtr value: " & StrPtr(S) sMsg = sMsg & vbLf & "You pressed Enter: " & (S = vbNullString) sMsg = sMsg & vbLf & "You entered: " & Chr(34) & S & Chr(34) End If MsgBox sMsg End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
"GS" skrev i melding ...
Opposed to other variables, VBA will not know how much memory a String will need. So what it does when a string is used is to save it "somewhere/anywhere available" and put a pointer, the address to the text, i variable S. When you cancel, the string is not used and the stringpointer, StrPtr, remains zero. Harald, Thanks for sharing an 'old trick'; these are always welcome! Unfortunately, it doesn't work when users "enter nothing" because doing so (leave empty, hit Enter key) returns an empty string (or vbNullString) and so StrPtr is not zero... I must have explained myself poorly. This is the whole point, separating cancelling from entering nothing. Entering nothing is a valid input in many operations.This works. Best wishes Harald |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
"GS" skrev i melding
... Opposed to other variables, VBA will not know how much memory a String will need. So what it does when a string is used is to save it "somewhere/anywhere available" and put a pointer, the address to the text, i variable S. When you cancel, the string is not used and the stringpointer, StrPtr, remains zero. Harald, Thanks for sharing an 'old trick'; these are always welcome! Unfortunately, it doesn't work when users "enter nothing" because doing so (leave empty, hit Enter key) returns an empty string (or vbNullString) and so StrPtr is not zero... I must have explained myself poorly. This is the whole point, separating cancelling from entering nothing. Entering nothing is a valid input in many operations.This works. Best wishes Harald Good point! It makes sense then to... If StrPtr < 0 And sz = vbNullString ...so we know user didn't cancel. I like it!! Thanks for sharing this... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Input Box error
In case you didn't take my example code as 'context only'...
If StrPtr(sz) < 0 And sz = vbNullString ...is the correct syntax!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Box Vs Cancel | Excel Programming | |||
Input Box - Hit cancel | Excel Programming | |||
Input box to cancel sub when Cancel is clicked. | Excel Programming | |||
Input box cancel | Excel Programming | |||
Input box cancel produces error | Excel Programming |