Cancel InputBox
 

Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result of the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula &
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

Alfredo_CPA wrote:
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result of the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click the
cancel button) and exits the sub, but I need the code to keep going

Thanks

This code demonstrates how the input value is interpreted:

Sub IPBOX()
Dim a As Variant
a = InputBox("prompt")
If a = "" Then
Debug.Print "canceled or empty input"
Else
Debug.Print Val(a)
End If
End Sub

Cancel InputBox
 

Replace
If MyAns = vbNo Then Exit Sub

with

If MyAns < vbYes Then Exit Sub


If this post helps click Yes
---------------
Jacob Skaria


"Alfredo_CPA" wrote:


Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula &
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

Hi

I suggest you use the InputBox Function rather than InputBox Method, as the
last will result in a zero if user press cancel.

MyErrorValue = _
InputBox("Enter the value you want to see instead of the error.", "Pick
Option")

Best regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...

Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want
to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
&
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel
in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result
of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click
the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

If you use the VB InputBox (note... it does *not* provide a Type argument...
all return values are Text), you can detect when the Cancel button has been
pressed. Give this a try to see how to distinguish between the various
options...

Sub Test()
Dim Response As String
Response = InputBox("Enter something:")
If StrPtr(Response) = 0 Then
MsgBox "You pressed the Cancel button!"
ElseIf Len(Response) = 0 Then
MsgBox "You pressed the Enter button without entering anything!"
Else
MsgBox "You entered this: " & Response
End If
End Sub

--
Rick (MVP - Excel)


"Alfredo_CPA" .(donotspam) wrote in message
...

Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want
to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
&
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel
in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result
of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click
the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

It doesn't work with cancel. Thanks anyway

"Jacob Skaria" wrote:

Replace
If MyAns = vbNo Then Exit Sub

with

If MyAns < vbYes Then Exit Sub


If this post helps click Yes
---------------
Jacob Skaria


"Alfredo_CPA" wrote:


Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula &
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click the
cancel button) and exits the sub, but I need the code to keep going

Thanks

Cancel InputBox
 

Thanks Per (and all the others) using the the InputBox Function rather than
InputBox Method solved my problem


"Per Jessen" wrote:

Hi

I suggest you use the InputBox Function rather than InputBox Method, as the
last will result in a zero if user press cancel.

MyErrorValue = _
InputBox("Enter the value you want to see instead of the error.", "Pick
Option")

Best regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...

Per,

When I hit cancel the code runs wrong. Here is the whole code:


Sub IfIserrorNew()
If IsEmpty(ActiveCell) Then
MsgBox " You Are In an Empty Cell ! Please select the cell with
the error value", 16, "My Friend"
Else
MyAns = MsgBox("Do you want to replace formula with ISERROR?",
vbYesNo + vbQuestion, "HIDE ERRORS??")
If MyAns = vbNo Then Exit Sub
myerrorvalue = Application.InputBox("Enter the value you want
to
see instead of the error." , "Pick Option", Type:=2)
MyOriginalFormula = ActiveCell.Formula
MyOriginalFormula = Right(MyOriginalFormula,
Len(MyOriginalFormula) - 1)
If myerrorvalue = "" Then
MsgBox "Nothing entered, or Cancel"
ElseIf IsNumeric(myerrorvalue) Then
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
& ")," & myerrorvalue & ",(" & MyOriginalFormula & "))"
Else
ActiveCell.Formula = "=IF(ISERROR(" & MyOriginalFormula
&
"),""" & myerrorvalue & """,(" & MyOriginalFormula & "))"
End If
End If

"Per Jessen" wrote:

Hi

Look at this:

Sub aaa()
a = InputBox("Enter a value or text")
If a = "" Then MsgBox ("No entry, or Cancel")
If IsNumeric(a) Then
msg = MsgBox("Number")
Else
MsgBox ("Text")
End If
End Sub

Regards,
Per

"Alfredo_CPA" .(donotspam) skrev i meddelelsen
...
Hi,

Does anybody knows how to instruct VBA to diferentiate between a Cancel
in
the InputBox and a cero entered in the impiut box? (excel 2003)
I have this code:
ElseIf myerrorvalue = False Then Exit Sub '(errorvalue is the result
of
the
inputbox)

VBA is taking a 0 (cero) value as False (just like if the user click
the
cancel button) and exits the sub, but I need the code to keep going

Thanks