Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •