Input Box - CANCEL

[Guest]

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.

 

[Guest]

Try:

Sub test()
Dim varResult As Variant

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

End Sub

 

[Corey]

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

 

[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

 

[Guest]

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!

 

[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

 

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