Input Box - CANCEL

D

Danny

Hi,

Please edit the macro below so when the user clicks on CANCEL or CLOSE the
box, Range A1 existing input won't change.

Sub InputNumber()
Range("A1").Value = InputBox("What is the number?")
End Sub

Thank you.
 
J

JMB

Try:

Sub test()
Dim varResult As Variant

varResult = InputBox("Input")
If IsNumeric(varResult) Then _
Range("A1").Value = varResult

End Sub
 
C

Corey

Sub InputNumber()
res = InputBox("What is the number?")
If res = "" Then
Exit Sub
End If
End Sub
 
G

Gord Dibben

Danny

I would tend to check for nothing entered or wrong data type entered.

Sub InputNumber()
selectnum = InputBox("What is the number?")
If selectnum = "" Or Not IsNumeric(selectnum) Then Exit Sub
Range("A1").Value = selectnum
End Sub


Gord Dibben MS Excel MVP
 
D

Danny

Gord,

Your macro is the one I used. I tried some variables so I can use formula in
the input box and came up with:

If selectnum = "" Then Exit Sub

Thank you all!
 
G

Gord Dibben

OK

I guess you have some other means of ensuring that non-numerics are entered.

Otherwise a text entry could give errors in your formulas if they reference A1.


Gord
 
C

Chip Pearson

If it there are different meanings to an empty string returned by the user
clicking OK and an empty string returned by the user clicking Cancel, use
code like the following:

Dim Res As String
Res = InputBox("What is the number?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered " & Res
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
Top