Don't move or size with cells not working


I have added a column of buttons to a worksheet (in a dashboard format) each of which has a macro assigned to it. Each macro adds a row to a certain section of the worksheet form, so that each time a button is clicked, a new row is added. Each button's properties are set to "Don't Move or Size with Cells", but sometimes the buttons vertically expand or contract, apparently on their own with no obvious pattern. My guess is that this is a bug in Excel (?) Any suggestions on how to prevent this from happening? One other forum I checked suggested creating the buttons with a macro (or setting the properties of the buttons with a macro) every time the worksheet is opened. Thank you for your help.

I have attached a sample worksheet with this post, showing how one of the buttons in the dashboard has compressed vertically.




I haven't been able to replicate the error you describe but here are some thoughts on the subject.

The buttons on the right aren't in cells. They are in a shape. That shape doesn't change size with the cells behind it, either (or does it?) but it appears that the buttons within the shape change size with the cells behind them. Could it be that the contraction of the container shape took place before you set that shape's resize property? If not, this would appear to indicate that either the buttons don't know which cells they are covering (which might indeed be an Excel bug) or you don't know, which might make it a matter of how the buttons where set up. Sometimes Excel is sensitive to sequence.

All your "buttons" are "shapes". My instinct tells me to suspect that they weren't intended for what you use them for. It might be unfair to call a malfunction a bug, for that reason. Try to avoid the Shape-in-Shape structure.

Logic reminds me that I didn't see the code that causes the changes in the sheet which cause the shapes to change shape. Try to post a workbook with full functionality.

Experience, on the other hand, tells me not to use shapes at all. Use ActiveX controls which were designed to achieve your objective. They can't be made quite as attractive as shapes but one advantage they do have is their Control Tip property which allows you to display the messages you have in rows 1:2 as the cursor hovers over each button. You may try placing Command Buttons within a shape, within a Label or within a TextBox. Command buttons don't change size with their environment.

Practise tells me to forget about the buttons. First they give you trouble setting up (hic), then they clutter up your UI. I would solve the problem with the Double-Click event. Double-click on "Annual Reviews" to add a row in that section etc. Use Comments to instruct the user if you must. Most users get the idea if told once.


Answer the Question

You must create an account to use the forum. Create an Account or Login