VBA macro in read-only workbook
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: VBA macro in read-only workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Chester, England
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've written/recorded a macro that copies certain sheets from one workbook to another. It works fine when I am the only person in the workbook or if I select to open it read only, but if there is someone else in the workbook when I open it then it fails.
    Trying to run the macro produces this error message:

    "Run-time error '1004': Select method of Sheets class failed"

    on this line:

    "Sheets(Array(4, 6, 9, 10)).Select"

    I have found a work-around by making the whole workbook read-only but I'd love to know why it does this.

    Can anyone put me out of my misery?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi JC3,

    I'm only guessing here, but I think it is a fairly educated one. When you select multiple sheets in a shared workbook, you are doing a fairly dangerous thing, because this implies that any change made to any of the selected sheets gets made to all of them. Another way of looking at this is that the active cell on the active worksheet becomes active on all the selected sheets, which means they are ready for data entry. Thus the select operation requires that all the sheets you select be locked so that you are the only one that can change them. But what if someone else is currently working on one of these sheets, and therefore already has it locked?

    It may be that because of this potential for problems, Excel does not allow multi-sheet selections in shared workbooks. Or, it could be that it just runs into a lock conflict if you and another user both try to lock the same sheet or cells at the same time. Either way, you get the same result.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com