VBA: worksheet object temporarily disappeared

[MerkDog]

Hi,

I have a simple macro that cycles through the sheets in a workbook, and if the sheet's codename matches one of a defined list, some of its data is added to a summary sheet.

The macro works exactly as intended, but a strange thing happened yesterday: some data was missing from the summary sheet because one of the sheets was being ignored. This sheet is named 'MCP' on its tab, and has codename Sheet8.

Here's the strange part: on stepping through the code, cycling the sheets, I noticed that the sheet icon, name and codename had disappeared from the Microsoft Excel Objects folder in the Project Explorer. When the loop got to the sheet in question

For Each ws In ThisWorkbook.Worksheets
   Debug.Print ws.Name
   Debug.Print ws.Codename

the above code displayed its name (correctly) as 'MCP', but its codename was blank; the sheet was therefore skipped by the code because the codename had to match against a defined list.

While I was pondering this, and doing some web searches, the sheet then re-appeared in the Project Explorer and everything worked again.

My question is this: has anyone experienced this behaviour before, and is it likely to be due to the workbook being shared? I know that workbook sharing in Excel is often discouraged, but this is a simple workbook only used by a maximum of three users (two of these had the workbook open at the time the issue was reported)

I've changed the code so that the sheet name is inspected in the event that the codename is blank, which should guard against the issue provided sheets are not deleted/renamed.

Any advice much appreciated.

MerkDog
Windows XP, Excel 2003

 

[RoryA]

is it likely to be due to the workbook being shared?

In my opinion, yes. I've never heard of such a thing but shared workbooks and strange behaviour tend to go hand in hand.

 

[MerkDog]

Thanks for your reply Rory, that confirms my suspicions. I always advise against sharing workbooks but sometimes to no avail.

 

[obiron]

If sharing workbooks look at the Excel application function Checkout()

this allows you to store a copy of the workbook on a central server and check it out to a local directory for editing, then check it back in. If it is checked out then other users can only view it but not make changes until it is checked back in, even if the user is not in the file. Any changes are made in the local copy which is then checked back in.

I've never used it but I think it might solve some of your issues.

 

[MerkDog]

Thanks Obiron, I'll take a look at that feature.