Cancel Input Box error

L

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

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
 
G

GS

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
 
L

L. Howard

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
 
C

Claus Busch

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")
Click to expand...

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.
 
L

L. Howard

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
 
G

GS

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
Click to expand...

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
 
L

L. Howard

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
Click to expand...

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!
Click to expand...

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
 
G

GS

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
 
L

L. Howard

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
Click to expand...


I'll give it a go.

Thanks Garry.

Howard
 
G

GS

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
 
L

L. Howard

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
Click to expand...



Yes, I caught the typo, and it works well with "cleaner" coding.

Thanks again.

Howard
 
H

Harald Staff

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" <[email protected]> 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
 
G

GS

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.
Click to expand...

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
 
H

Harald Staff

GS said:
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...
Click to expand...

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
 
G

GS

GS said:
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
Click to expand...

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
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to hide password? 1
Somewhat complex code to copy data from one sheet to another 9
Copy FROM - TO Object/Block variable not set 6
InputBox to name a range 2
Cancel (Dismiss) Input Box 1
Input box cancel 1
Excel Message box called if 1 of 3 options is entered. 4
Display Visible sheets Only within Input Box 8

Top