 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / December 2006Hitting 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 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 > Dear Experts: > [quoted text clipped - 45 lines] > > Andreas 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. > You could handle all situation with your error handler "Mistake." > [quoted text clipped - 88 lines] >> >> Andreas 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? > 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 [quoted text clipped - 102 lines] > >> > >> Andreas 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 > Jezebel, > [quoted text clipped - 157 lines] >> >> >> >> Andreas 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.  SignatureGreg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. > I'd avoid the dreaded GOTOs also. A simpler construction is a loop, > that exits when the value is valid or the user cancels .. [quoted text clipped - 174 lines] >>>>> >>>>> 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 schrieb: > Karl, Jezebel: > [quoted text clipped - 227 lines] > >>>>> > >>>>> Andreas 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. > Dear Greg, > [quoted text clipped - 241 lines] > > >>>>> > > >>>>> 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 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 .. [quoted text clipped - 174 lines] > >> >> > >> >> Andreas > 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 [quoted text clipped - 3 lines] > 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? SignatureWorking without a .NET? http://classicvb.org/ Karl, Thanks for the note and example of significantly more readable code. > > 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 [quoted text clipped - 25 lines] > What I'm curious about is what sort of error you're attempting to catch, > here? 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: > > 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 [quoted text clipped - 25 lines] > What I'm curious about is what sort of error you're attempting to catch, > here? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.