subscript out of range

Topic

New Reply

Anybody have and idea why I would get a subscript out of range error with the below piece of code. The workbook name is called CHEX. This only happens for 1 user. It does not happen for all other users. I’m really confused on what I can do to resolve this.

‘ Hide all worksheets
Dim Wks As Worksheet
For Each Wks In Workbooks(“CHEX”).Sheets
If Wks.Name “MAIN” Then
Wks.Visible = False
End If
Next Wks

I’m basically trying to hide all sheets when the work book is opened except for the main sheet. Any ideas or suggestions is appreciated.

Reply | Quote

Replies

You didn’t mention the xl version you are using or on what line the error occurs.
My guess is that…
For Each Wks In Workbooks(“CHEX”).Sheets

Should be…
For Each Wks In Workbooks(“CHEX.xls”).Sheets
‘–
Jim Cone
Portland, Oregon USA
XL Companion add-in

Reply | Quote

It is always better to include the file extension, as Jim showed you. I would say that your one user has Explorer set to show file extensions and that’s why the code wasn’t working for him. If you include the extension it should work either way.

Reply | Quote

Thanks for the suggestion. Sorry it was Excel 2003 and the error occured at the line that contained “For each WKS in Workbooks(“CHEX”).Sheets”.
I’m not sure why I didn’t include the extension but because it worked for me and other users. It didn’t seem like it was truely needed. I’ll try and see. Thanks again!

Reply | Quote