Desktop Upgrades causeing macro crash

elborto

New Member
Joined
Jul 30, 2007
Messages
1
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

rbrhodes

Board Regular
Joined
May 25, 2007
Messages
69
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

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

Forum statistics

Threads
1,181,365
Messages
5,929,548
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top