Run-time error 9: Subscript out of range

[vasim]

I have five different users using different workbooks (front end); at the end of day they need to click “update database macro” which compiles all their data in a different workbook (back end).


Sub add_to_master_sheet()

If MsgBox("Update Database to reflect data from this file?", vbYesNo, "Add to Database") = vbYes Then

Application.ScreenUpdating = False

Workbooks.Open Filename:= _

"workaholixnew Rec-Reports backend.xlsm", writeresPassword:="password"

Workbooks("backend").Activate '(Error throws up here)

Sheets("Database").Select

‘more codes

End sub


For all the user the code is working fine except at one of them, it throws up an error as “Run-time error 9: Subscript out of range”.


If the same file(error one) is used from other PC (shared network) it works fine.

If any other users front end sheet is updated from her desk it throws up an error.


Any help please??


PS: I have checked the workbook name...its the same...and thats why doesnt have a problem while opening but only when activating...

 

[NARAYANK991]

Hi ,


Do all users who are running the above macro(s) , have the same version of Excel ? Or at least , is the user who is having a problem with the above macro , running the same version of Excel as all the others ?


Narayan

 

[vasim]

Thanks Narayan, all user have MS Excel 2007...

 

[Marc L]

Try this :

[pre]

If MsgBox("Update Database to reflect data from this file?", vbYesNo, "Add to Database") = vbYes Then
Application.ScreenUpdating = False
Set Wb = Workbooks.Open("workaholixnewRec-Reportsbackend.xlsm", writeresPassword:="password")
Wb.Activate
Sheets("Database").Select

[/pre]

 

[vasim]

Thanks All....finally a sigh of releif


Workbooks("backend").Activate

changed to

Workbooks("backend.xlsm").Activate


its working fine at her desk as well.....


Just saved my life....

 

[Marc L]

Using variable object avoid this kind of problem …

 

[vasim]

Thanks Marc....will keep a tab in future

 

[Colin Legg]

I think this is because your colleague has the Windows hide file extensions setting turned off. Have a read of this article:

http://www.cpearson.com/excel/FileExtensions.aspx


where Chip pearson says:

As a matter of good programming practice, you should always include the xls extension in a workbook name. This ensures that you reference the correct workbook regardless of the value of the hide extensions property.

 

[shrivallabha]

I also had had this problem in the past. The problem with RTE9 is that it applies to varying cases [index larger than array size, mismatches in names etc.) and it therefore becomes little difficult to trace the root cause.


http://www.vbaexpress.com/forum/showthread.php?t=32359&highlight=runtime+error+9