End Sub if InputBox cancelled (False)

[Francis Hookham]

Input Box Cancel button should stop the subroutine.



OK button tests to see if 'x' has been changed.



I cannot see what is wrong here:



LastUsedRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row

DoorNum = Cells(LastUsedRow, 5)

x = InputBox("Door number?", _

"Additional items to door", DoorNum, 1)

If x = False Then

End

ElseIf x <> DoorNum Then

DoorNum = x

End If



Francis Hookham

 

[George Nicholson]

Input Boxes return strings, not T/F. A Cancelled Input box returns an empty
string: ""

Select Case x
Case ""
' User Canceled
Exit Sub
Case DoorNum
' x hasn't changed: do nothing
Case Else
' x has changed
DoorNum = x
End select

 

[Guest]

I think that when you cancel an input box, a zero length string is returned.
As such, replace this:

If x = False Then

with this:

If x = "" Then

-Cory

 

[Guest]

If you use Application.InputBox the Cancel will = False
but with InputBox Function, Cancel = ""

 

[Francis Hookham]

Many thanks George and Cory - all's well now

I was trying to use False because I read in VBA Help - InputBox Remarks:

If you choose the OK button, InputBox returns the value entered in the
dialog box. If you click the Cancel button, InputBox returns False.

Francis Hookham

 

[George Nicholson]

The Entry in Excel's Help for the Application.InputBox method says that, but
the entry for the VBA InputBox function (in both Access and Excel) says:

"If the user clicks Cancel, the function returns a zero-length string ("")."

At least, that's what I am seeing (using Office 2003)

Not specifying Application means the VBA function will be used, not the
Excel method.

Be careful which Help entries you are reading. Very similar objects,
properties etc. can really be different under the surface across apps.
*Usually* those differences are confined to how a few functions calculate or
Forms (Access Forms live in their own little universe when compared to VB
forms or Office Forms), but there are a few other "gotchas". This is
evidently one of them.

HTH,