Shared Workbook Splitting Merged Cells

[MattB29]

I am using Excel 2010 on a Windows 7 Pro OS.

I have developed a simple weekly schedule spreadsheet for use by our project managers to help allocate junior engineering resources. There are two different divisions within the company and I have simple macro buttons that will hide/show the approriate rows for each division.

Instead of having absolute row references (i.e. 10 or 57), I have the macro search for the division name, which are in column A and are merged to encompass all appropriate rows. This way if I need to add/delete a division team member I won't have to change the row references each time in the VBA code.

The spreadsheet works without flaw as an unshared workbook or as a shared workbook with a single user. But when the workbook is shared and two users are in it simultaneously, if one person saves it and then another person saves it (not simultaneously, but in close succession) the merged cells in column A split, which ruins how my VBA code executes.

In addition, I have each worksheet protected and allow only unlocked cells to have minimal formatting allowed to them. The merged cells in column A are locked and therefore when protected/shared they cannot be formatted or even selected by the user.

So is there possibly a better way to code my macro? Or is it that in some way Excel on its own is splitting the merged cells?

Any help would be greatly appreciated,

Matt B.

 

[Smitty]

Welcome to the Board!

The simple answer is not to use Merged Cells. Not only do they suck, they cause all kinds of problems with VBA code. You can use Center Across Selection instead, which leaves the cells intact.

As for protection, it's not programmatically supported in Shared workbooks (which suck as well).

You might want to look into using SkyDrive, which does allow a degree of collaborative editing: http://explore.live.com/skydrive-share-photos-files?T1=t4.

HTH,

 

[MattB29]

Thanks for your prompt response Smitty.

I looked into your solution and I believe, unfortunately, that it won't work. I need to center the text vertically among a certain number of rows. As far as I can tell the center across selection only works horizontally.

As to your comment about the limitation of sharing workbooks - I think using SkyDive is above and beyond what my PMs are looking to use.

That being said, I am surprised that such a simple spreadsheet/macro can become so glitchy when sharing the workbook. I am at a loss for any other way to reformat my cells or recode my macro so that it will work while being shared, and I don't want anyone to take too much time or effort to think of an alternative approach (excel based only) but if something does come to mind I would appreciate hearing it.

Thanks again,

Matt B.

 

[Smitty]

You're right, you can only center across horizontally, so that's out. You might be able to use a text box set to move & size with cells.

The next step is to post your code, so we can see what's going on.

FYI - I'm probably out of here for the day, so don't think me rude if I don't respond tonight.

 

[MattB29]

The text box with the move and size with cells option might work, I will have to look into it further.

I will definitely report my results as soon as I have them and let you know if it resolved my problem or not.

Thanks again for your help!

-Matt B