Buttons disappear when rows are hidden/unhidden in 2010

In an Excel 2010 worksheet with command buttons set to "Move and Size with Cells", if you hide the columns they are in (which also hides the buttons), then save and close the workbook, when you reopen the workbook and unhide the columns, the button widths remain at 0, making them invisible. The problem will not show up until you have closed and reopened the workbook. If you save it with the columns hidden, then unhide them, the buttons reappear as they should, but then if you close (without saving) and reopen the workbook, they're gone.

The only way to show them, other than resetting the width with code, is to go to design mode, select another button or object, and hit Tab until it lands on the hidden object. Then you can drag it wider.

My app does a lot of column hiding & unhiding, as well as saving and routing through Outlook with attachments, so I don't see a reasonable VBA solution. Whenever it unhides columns it would have to loop through all the objects, try to determine if any were in the hidden columns, and then somehow determine what the original width was. I suppose it's doable, but not reasonable or acceptable in my opinion.

Has anyone encountered this? Is there a reasonable workaround or solution to this bug?
It's not a problem in 2003 where I developed the app, but even if I create new buttons in 2010 and save it as a .xlsm, it still does the same thing. I took half a day digging through my code because I thought it was a VBA problem, but finally realized I could reproduce it manually. I would really appreciate some help with this.

Thanks for the response. At least I know I'm not crazy (though after spending half a day figuring out what the problem was, and another half a day developing a workaround, I'm getting close). It was more challenging than I thought it would be to make it work, because not only do the objects lose their width. They also, in some cases, lose their .left property and get stacked on top of each other.

I thought it might be helpful to someone else to provide the code I used to make it work. I hope it makes sense out of context. It's for an employee performance appraisal system, if that's any help.

Also, the objects' Format Control properties must be set to "Move and size with cells".

Please Login or Register  to view this content.

I have this problem too. I wrote vba to ungroup everything before saving, and then regroup, but even with turning off screen updating it doesn't look very good.

Originally Posted by arlu1201

Ctrlshiftenter (nice user id )

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Thanks for the welcome.

I'm not sure how I broke Rule 2. It wasn't a question. It was one fix to the problem with the the mentioned draw backs.

Hey, I thought I'd let you know that I see the same behavior (Excel 2010). It seems like when you have a button with the "Move and Size with Cells" property set, they're somehow left in the minimzed/hidden state even after expanding a set of grouped rows. Once you change that property to something else like "Don't move or size with cells", the buttons look awkward when you minimize the grouped rows.

I'm going to just try to manage without minimizing the grouped cells. I imagine you could add some handling that forces the button to resize to the width/height of the cell after you expand the grouped rows... but doesn't sound easy :-\

I ended up showing all outline levels before saving and then setting it back after saving or when opening (using Workbook_BeforeSave, Workbook_AfterSave, and Workbook_Open). You can see it expand and close when you save, which is unsettling, but it works.

HTML Code: 

To anyone that comes here, to stop your button from vanishing with hiding/filtering cells/rows/etc:

Right click button -> Format Control -> Properties Tab -> "Don't move or size with cells"

Everyone above is wrong or refer to non-general solutions. Now, I will assume that the hiding and unhiding is a part of a macro, since buttons at all are in the question.

There are two elements to the general solution. One has already been presented.

It is possible to format the buttons so that they do not size with the sheet. As someone has mentioned, this makes them hang awkwardly atop hidden rows. Therefore, we need to add lines of code to hide the button together with hiding the lines. Now you will get invisible (and unclickable) buttons that do not change their size as rows are hidden and unhidden. In order to hide a button you use the following code lines:

ThisWorkbook.Sheets("Your_sheetname").Shapes("Button 7").Visible = False

and

ThisWorkbook.Sheets("Your_sheetname").Shapes("Button 7").Visible = True

Granted, Izandol.