PDA

View Full Version : Solved: How to Clear Runtime Error 9



DCSwearingen
11-29-2006, 02:05 PM
I have a workbook with a lot of named values used in Energy conversions.

One of my associates in another state has been told my conversions are the most accurate in the company and to import them into his worksheets.

I set up a macro to do this.


Sub ApplyNamedFormulas()
Dim tBook As String, tryAgain As Long
mySubStart:
Err.Clear
tBook = InputBox("Enter target workbook name", "Enter Conversion Names Into Different Workbook")
If Right(tBook, 4) = ".xls" Then
myLen = Len(tBook) - 4
tBook = Left(tBook, myLen)
End If
On Error GoTo e_Msg
Windows(tBook).Activate ' <--- This is where I get the runtime error, but not the first time
Application.Run "ApplyConversionNames.xls!InitializeConversions"
Windows("ApplyConversionNames.xls").Activate
Exit Sub
e_Msg:
tryAgain = eMsg()
Select Case tryAgain
Case 6: GoTo mySubStart
Case Else: Exit Sub
End Select
End Sub

Private Function eMsg()
Dim myResp, myMsg As String
myMsg = "The target workbook entered does not appear to be open. Do you want to try again?"
myResp = MsgBox(myMsg, vbYesNo)
eMsg = myResp
End Function

The e_Msg will loop back to the beginning if an incorrect workbook name is entered. But if a typo is made a second time my code crashes with Runtime error 9, even with the line Err.Clear at the beginning of the macro.

How can I make the On Error GoTo e_Msg work no matter how many times the file name is mis-spelled?

Simon Lloyd
11-29-2006, 02:56 PM
Hi, im no expert but i believe you get runtime error 9 when the worksheet/book does not exist! or the worksheet has had its name changed, you may also get it if it is calling or expecting to run an add-in that has been turned off.

Regards,
Simon

mdmackillop
11-29-2006, 03:14 PM
Try
Case 6: Resume mySubStart

DCSwearingen
11-29-2006, 03:36 PM
Thanks a lot,
Case 6: Resume mySubStart
resolved the issue, now the TryAgain works repeatedly until a coorect name is entered.

mdmackillop
11-29-2006, 03:45 PM
Glad to hear it. Have a look at the Article on Error Handling by Johnske. A lot of useful stuff.