Error Handler for Run-time error '9'

[josnah]

I have a macro which works on a specific worksheet in a shared file.

Say the worksheet has been renamed from Test to something else by another user........
How can I set a Msg box to appear instead of the MIcrosoft VB dialog box displaying the following msg?

Run-time error '9'
Subscript out of range

I have tried the following using my trial & error method:

Sub Test()

On Error Got to ErrHandler1
Sheets("Test").Select

Exit Sub

ErrHandler1:
MsgBox "The Test worksheet is not found. Unable to proceed!"
Exit Sub

End Sub
Pls help!

[Carim]

Hi,

I am sure you know the combination of macros with shared files is quite complex ...
A macro can properly run in a shared file if the macro was saved before the worksheet became shared ...
With the problem you are describing, you should either instruct your users or pre-program the impossibility to change worksheet name ...

HTH
Carim

[josnah]

actually i meant that the file is being shared in a network folder. It can only be opened as Read-only to prevent modifications to the original file. but some actually save it in another location & make changes to the worksheet names. The macro then does not work.
The feature Workbook Sharing was not used.... Apologies for causing the confusion.

instruct the users? there are some stubborn people in my office so that just doesn't work!
pre-programming the impossibility to change worksheet name sounds interesting but how do i do it?

Anyways, anyone out there who can help me with the Msgbox macro

Thks!
Josnah