On an inputbox, I am asking the user to enter a number, and there is an OK
and a Cancel. I want it to exit the sub if the user hits Cancel, even if he
has already typed something in the box.

I just went through this last week. Check out this from Microsoft:

http://support.microsoft.com/?kbid=142141

you need to be using application.InputBox instead of just InputBox

Die_Another_Day

thanks

"Die_Another_Day" wrote:

I just went through this last week. Check out this from Microsoft:

http://support.microsoft.com/?kbid=142141

you need to be using application.InputBox instead of just InputBox

Die_Another_Day

Even in your article it shows you don't have to use the Application.Inputbox

' See if Cancel was pressed.
If Response = "" Then

' If Cancel was pressed,
' break out of the loop.
Show_Box = False

--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

I just went through this last week. Check out this from Microsoft:

http://support.microsoft.com/?kbid=142141

you need to be using application.InputBox instead of just InputBox

Die_Another_Day

The problem I have with VBA inputbox is that it doesn't seem to know
the difference between pressing ok and leaving the input blank or
pressing cancel. Sometimes I tell the user to leave the box blank if
they want a specific action. Is there a way to see the difference?

Die_Another_Day

While this wasn't an issue for the Original poster, yes there is a way:

Dim strInput As String
strInput = InputBox("do something")
If Len(strInput) = 0 Then
If StrPtr(strInput) = 0 Then
MsgBox "The user clicked Cancel"
Else
MsgBox "The user clicked Enter, but typed nothing"
End If
End If

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

The problem I have with VBA inputbox is that it doesn't seem to know
the difference between pressing ok and leaving the input blank or
pressing cancel. Sometimes I tell the user to leave the box blank if
they want a specific action. Is there a way to see the difference?

Die_Another_Day

Just test the result of the InputBox.

Dim S As String
S = InputBox("enter sometime")
If S = "" Then
Debug.Print "no input"
Exit Sub
Else
Debug.Print S
End If

This will not distinguish between the user pressing cancel and
the user pressing Enter with an empty input box. If you *really*
need to test for the Cancel key, use code like


Dim S As String
S = InputBox("enter something")
If StrPtr(S) = 0 Then
Debug.Print "user clicked cancel"
Else
Debug.Print "user click OK " & S
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mike" wrote in message
...
On an inputbox, I am asking the user to enter a number, and
there is an OK
and a Cancel. I want it to exit the sub if the user hits
Cancel, even if he
has already typed something in the box.

Clicking on cancel in an inputbox should return an empty string, even if you
provide default response text.

sub test

dim strRet as string
strRet=inputbox("What would you like me to echo back?")

if len(strRet) =0 then
exit sub
else
msgbox "You asked me to echo:" & strRet
end if

end sub

"Mike" wrote in message
...
On an inputbox, I am asking the user to enter a number, and there is an OK
and a Cancel. I want it to exit the sub if the user hits Cancel, even if
he
has already typed something in the box.

this is how to use the application.input box:

Sub iBox()
Dim var1 As Variant
var1 = Application.InputBox("Enter Text", "Testing Cancel Button")
If var1 = "False" Then
MsgBox "You Clicked Cancel"
Exit Sub
Else
MsgBox "You typed " & var1
End If
End Sub

Die_Another_Day

the application.Inputbox will certainly work, but there is no requirement to
use it in this case. The VBA inputbox will work fine as AMDRIT has shown.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

this is how to use the application.input box:

Sub iBox()
Dim var1 As Variant
var1 = Application.InputBox("Enter Text", "Testing Cancel Button")
If var1 = "False" Then
MsgBox "You Clicked Cancel"
Exit Sub
Else
MsgBox "You typed " & var1
End If
End Sub

Die_Another_Day

Sub test()

Dim vNumber
Dim dNumber As Double

vNumber = Application.InputBox(Prompt:="Put a number in the box",
Type:=1)

If vNumber = "False" Then
Exit Sub
Else
dNumber = Val(vNumber)
MsgBox dNumber
End If

End Sub


RBS


"Mike" wrote in message
...
On an inputbox, I am asking the user to enter a number, and there is an OK
and a Cancel. I want it to exit the sub if the user hits Cancel, even if
he
has already typed something in the box.