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?
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?