VBA macro in read-only workbook

JC3

New Member
Joined
Mar 5, 2002
Messages
2
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top