Desktop Upgrades causeing macro crash

[elborto]

Hi,
I hope you can help me on this issue because it is a sticky one for me. I write a number of macros for a client of my company. The client recently went through a desktop upgrade which has causd many of my ordinarily working macros to crash.
I am unsure of all the changes they have made but i am aware that no user has access to their c:\drive anymore. Can this have an impact on the running of a macro?
The error is a runtime error 9 "Subscript out of range" and the line in the code where the macro crashes is on a simple workbooks("workbook name").activate command.

Any help you can give me on this would be greatly appreciated.
Thanks,
Barry

 

[rbrhodes]

Hi Barry,

One thing comes to mind. If it is a new workbook it must be referered to as bookname. Once it has been saved it must be refered to as bookname.xls. ie:

Unsaved:

Workbooks("Book2").Activate

Saved:

Workbooks("Book2.xls").Activate


You could handle this error with an error checking routine (an If) ie:


'*******************
Option Explicit

Sub wbActivator()

'Error message if fail both
Dim eMsg As Long

'clear previous errors
Err.Clear

'allow error if "subscript out of range"
On Error Resume Next

'attempt new book
Workbooks("Book2").Activate

If Err <> 0 Then
'Was error so clear error and try again
Err.Clear
'attempt saved book
Workbooks("Book2.xls").Activate
If Err <> 0 Then
'Book2 is not open!
eMsg = MsgBox("Houston, we have a problem...", vbCritical)
Exit Sub
End If
End If

'no errors, continue on...
On Error GoTo 0

End Sub

'******************