[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

[GS[_2_]]

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

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

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

[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

[GS[_2_]]

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

[L. Howard]

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

[GS[_2_]]

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. Howard]

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

[GS[_2_]]

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. Howard]

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

[GS[_2_]]

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

[Harald Staff[_8_]]

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

[GS[_2_]]

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

[Harald Staff[_8_]]

"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

[GS[_2_]]

"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

[GS[_2_]]

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