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

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
Wed Dec 06 07:12:23 CST 2006

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




andreas wrote:
> 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

Jezebel
Wed Dec 06 07:47:53 CST 2006

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" <gmaxey@mvps.org> wrote in message
news:1165410743.034274.9790@16g2000cwy.googlegroups.com...
> 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
>
>
>
>
> andreas wrote:
>> 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
Wed Dec 06 08:43:59 CST 2006

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?


Jezebel wrote:
> 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" <gmaxey@mvps.org> wrote in message
> news:1165410743.034274.9790@16g2000cwy.googlegroups.com...
> > 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
> >
> >
> >
> >
> > andreas wrote:
> >> 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
> >

Karl
Wed Dec 06 14:02:17 CST 2006

andreas wrote:
> 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?
--
Working without a .NET?
http://classicvb.org/

Greg
Wed Dec 06 14:17:04 CST 2006

Karl,

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


Karl E. Peterson wrote:
> andreas wrote:
> > 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?
> --
> Working without a .NET?
> http://classicvb.org/

Jezebel
Wed Dec 06 14:39:27 CST 2006

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" <gmaxey@mvps.org> wrote in message
news:1165416239.423809.132380@79g2000cws.googlegroups.com...
> 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?
>
>
> Jezebel wrote:
>> 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" <gmaxey@mvps.org> wrote in message
>> news:1165410743.034274.9790@16g2000cwy.googlegroups.com...
>> > 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
>> >
>> >
>> >
>> >
>> > andreas wrote:
>> >> 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
Wed Dec 06 17:20:43 CST 2006

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.



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Jezebel wrote:
> 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" <gmaxey@mvps.org> wrote in message
> news:1165416239.423809.132380@79g2000cws.googlegroups.com...
>> 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?
>>
>>
>> Jezebel wrote:
>>> 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" <gmaxey@mvps.org> wrote in message
>>> news:1165410743.034274.9790@16g2000cwy.googlegroups.com...
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>> andreas wrote:
>>>>> 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

andreas
Wed Dec 06 23:13:37 CST 2006

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


Karl E. Peterson schrieb:

> andreas wrote:
> > 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?
> --
> Working without a .NET?
> http://classicvb.org/

andreas
Wed Dec 06 23:16:23 CST 2006

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


Jezebel schrieb:

> 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" <gmaxey@mvps.org> wrote in message
> news:1165416239.423809.132380@79g2000cws.googlegroups.com...
> > 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?
> >
> >
> > Jezebel wrote:
> >> 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" <gmaxey@mvps.org> wrote in message
> >> news:1165410743.034274.9790@16g2000cwy.googlegroups.com...
> >> > 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
> >> >
> >> >
> >> >
> >> >
> >> > andreas wrote:
> >> >> 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
> >> >
> >

andreas
Wed Dec 06 23:24:35 CST 2006

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 schrieb:

> 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.
>
>
>
> --
> Greg Maxey/Word MVP
> See:
> http://gregmaxey.mvps.org/word_tips.htm
> For some helpful tips using Word.
>
>
> Jezebel wrote:
> > 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" <gmaxey@mvps.org> wrote in message
> > news:1165416239.423809.132380@79g2000cws.googlegroups.com...
> >> 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?
> >>
> >>
> >> Jezebel wrote:
> >>> 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 ou