Hitting Cancel Button on Input Box takes me to "GoTo Error MsgBox" instead of exiting the macro

[andreas]

Dear Experts:

I created a macro that allows the user to format the Table of Contents
by using InputBoxes.

The user can fill in a value into the dialog box "Please indicate the
left indent in centimeters". The value he/she indicates is then
transferred to the "With ... " line.

I then want the the user to see the previously filled in value (in
centimeters) whenever he re-runs the macro. It works fine with the
following macro lines (only part of the whole macro) but as soon as I
hit the Cancel Button on the InputBox form it does not exit the sub but
takes me to the Error Message on the bottom. How do I have to rewrite
the code that - when the user hits the Cancel Button on the inputbox
form - the macro is exited?

Dim LeftIndent As Single

LeftIndent =
Round(PointsToCentimeters(ActiveDocument.Styles_("Custom_TOC_Style").ParagraphFormat.LeftIndent),
1)

On Error GoTo Mistake
'since I declared the LefIndent as single, entering character(s) or
blank(s) causes the macro to jump to the Error Message below

LeftIndent = InputBox("Please indicate the left Indent", "Indent Size",
LeftIndent)
If LeftIndent <= 1.3 Then
MsgBox "No values below 1.3 are allowed. "
Exit Sub
End If

With ActiveDocument.Styles("Custom_TOC_Style").ParagraphFormat
.LeftIndent = CentimetersToPoints(LeftIndent)
.FirstLineIndent = CentimetersToPoints(-LeftIndent)
End With

Exit Sub
Mistake:
MsgBox "only numbers are allowed, no blanks or characters",
vbInformation

End Sub

Help is appreciated. Thanks in advance

Andreas

 

[Greg Maxey]

You could handle all situation with your error handler "Mistake."

I picked Err.Raise 93 "Invalid string pattern" as it seemed to be the
closest trapable error to your requirement. I have never used this
method before but it seems to work. Perhaps one of the more
experienced "experts" ;-) will come along and set up both straight.


Sub Test()
Dim LeftIndent As Single
Dim strInput As String
LeftIndent =
Round(PointsToCentimeters(ActiveDocument.Styles("Normal").ParagraphFormat.LeftIndent),
1)
On Error GoTo Mistake
Retry:
strInput = InputBox("Please indicate the left Indent", "Indent Size",
LeftIndent)
LeftIndent = CSng(strInput)
If LeftIndent <= 1.3 Then
Err.Raise 93
End If
With ActiveDocument.Styles("Custom_TOC_Style").ParagraphFormat
.LeftIndent = CentimetersToPoints(LeftIndent)
.FirstLineIndent = CentimetersToPoints(-LeftIndent)
End With
Exit Sub
Mistake:
If Not IsNumeric(strInput) Then
If strInput = "" Then
MsgBox "Canceled by user"
Exit Sub
Else
MsgBox "You must use a numeric input greater than 1.3."
Resume Retry
End If
Else
If Err.Number = 93 Then
MsgBox "You must use a numeric input greater than 1.3."
Resume Retry
End If
End If
End Sub

 

[Jezebel]

If you raise your own errors you should use values vbObjectError (built in
constant) and upwards, to avoid conflicts with the built-in errors; and set
your own description --

Err.Raise Number:=vbObjectError, Description:="User cancelled..."

But as someone else pointed out in a recent post, it's generally better to
use in-line error-handling for this kind of situation (using on error resume
next). The difficulty -- and bug generator -- with the code you're
suggesting here is that the error-handler is making assumptions about what's
triggered the error. This is a dangerous assumption to make, even in this
simple example. See what happens if "Custom_TOC_Style" is missing from the
document.

 

[Greg Maxey]

Jezebel,

Thanks. So with that line of thinking I shouldn't have to raise and
error at all. I could use something like this:

Sub Test()
Dim LeftIndent As Single
Dim strInput As String
On Error GoTo Err_Handler
LeftIndent =
Round(PointsToCentimeters(ActiveDocument.Styles("Normal").ParagraphFormat.LeftIndent),
1)
On Error GoTo 0
Retry:
On Error Resume Next
strInput = InputBox("Please indicate the left Indent", "Indent Size",
LeftIndent)
If strInput = "" Then
Exit Sub
End If
LeftIndent = CSng(strInput)
Select Case Err.Number
Case 13
MsgBox "You must use a numeric input greater than 1.3."
GoTo Retry
Case 0
If LeftIndent <= 1.3 Then
MsgBox "You must use a numeric input greater than 1.3."
GoTo Retry
End If
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
With ActiveDocument.Styles("Normal").ParagraphFormat
.LeftIndent = CentimetersToPoints(LeftIndent)
.FirstLineIndent = CentimetersToPoints(-LeftIndent)
End With
Exit Sub
Err_Handler:
If Err.Number = 5941 Then
MsgBox "The style ""XXXX"" is not found in this document."
End If
End Sub

Is this more appropriate for the situation?

 

[Karl E. Peterson]

I then want the the user to see the previously filled in value (in
centimeters) whenever he re-runs the macro. It works fine with the
following macro lines (only part of the whole macro) but as soon as I
hit the Cancel Button on the InputBox form it does not exit the sub
but takes me to the Error Message on the bottom. How do I have to
rewrite the code that - when the user hits the Cancel Button on the
inputbox form - the macro is exited?

As Jezebel correctly points out, the real error here is of your own
making -- you're not testing things that are fully within your control to
test, but instead acting blindly.

When a Cancel button on an InputBox is pressed, the return value is
vbNullString. (Note to Greg: This is *not* synonamous with ""!) Just test
for that, if you want to know whether Cancel was pressed. IOW...

Dim sReturn As String
sReturn = InputBox("Please indicate the left Indent", "Indent Size",
LeftIndent)
If sReturn = vbNullString Then
Exit Sub 'Cancel pressed!
ElseIf Val(sReturn) < 1.3 Then
MsgBox "Only numeric values (greater than or equal to 1.3) are allowed."
Else
LeftIndent = Val(sReturn)
End If

What I'm curious about is what sort of error you're attempting to catch,
here?

 

[Greg Maxey]

Karl,

Thanks for the note and example of significantly more readable code.

 

[Jezebel]

I'd avoid the dreaded GOTOs also. A simpler construction is a loop, that
exits when the value is valid or the user cancels ..

numInput = 0
Do
strInput = InputBox("Please enter the left indent (> 1.3)", "Indent
Size", LeftIndent)
if len(strInput) = 0 then
exit sub
end if

on error resume next
numInput = csng(Input)
on error goto 0

Loop while numInput < 1.3

 

[Greg Maxey]

Karl, Jezebel:

It appears that I am more interested in this topic than the OP ;-)

Looking at all of your comments, it seems to me the best code for what I
would want to occur would be this:


Sub ScratchMacro()
Dim sngIndex As Single
Dim oStyle As Styles
Dim sReturn As String
Dim numReturn As Single
Set oStyle = ActiveDocument.Styles
sngIndex =
Round(PointsToInches(oStyle("Normal").ParagraphFormat.LeftIndent), 1)
numReturn = 0
Do
sReturn = InputBox("Enter the left Indent value." & vbCr & vbCr _
& "Only numeric values (greater than or equal to 1.3) " _
& "are allowed.", "Indent value", sngIndex)
If sReturn = vbNullString Then
Exit Sub 'Cancel pressed!
End If
On Error Resume Next
numReturn = CStr(sReturn) 'Non-numeric entries will trigger a type
mismatch
If Err.Number = 13 Then
MsgBox "Only numeric values (greater than or equal to 1.3) are
allowed.", _
vbCritical, "Invalid Entry"
On Error GoTo 0
ElseIf numReturn < 1.3 Then
MsgBox "Your entry was too small. Please enter a value greater than or
" _
& "equal to 1.3.", vbInformation, "Invalid Entry"
End If
Loop While numReturn < 1.3
sngIndex = numReturn
On Error GoTo 0 'Negates the earlier Resume Next statement
With oStyle("Normala").ParagraphFormat
.LeftIndent = InchesToPoints(sngIndex)
.FirstLineIndent = InchesToPoints(-sngIndex)
End With
End Sub

Do either of you see any foopahs in this code? Thanks.

 

[andreas]

Dear Karl,

thank you very much for your input. Greg integrated your part of the
code into the macro and it is just running fine. Good job.

Regards,

Andreas

 

[andreas]

Jezebel,

thank you for your great input. As you know your suggestions have been
integrated into Gregs Macro Code and it is running just. Fine. Good
job.

Regards,

Andreas

 

[andreas]

Dear Greg,

it is working just fine. Very good job. What I will never understand is
how you and other experts are able to come up with macro code solutions
so quick. Incredible.

I may have another related problem and come back to the forum

Regards,

Andreas

 

[Greg Maxey]

Andreas,

Thanks for the feedback. First I am more an intermediate novice than
expert. I learn with the help of the real experts, of which I number
Jezebel, Karl and a dozen or so others that grace this group, by trying
to answer questions in these newsgroups.

I suppose that using the language of VBA is a bit like any other
language. Take Mandarin Chinese for example. Effortless for native
speakers. A student like me may be able to express common everyday
phrases with little effort but has crawl and scratch through the more
difficult expressions learning a bit more along the way. It is
jibberish to anyone who has never heard it.