Buttons resizing in excel-vba on paste

Question

I have a macro that performs a number of functions and works properly, but the buttons resize in one of two situations. The buttons reize when the macro uses an autofilter, which deletes roughly 500 rows of data (the buttons get much larger), and they also resize when I first paste in the data before I run the program (once again get larger). I have tried going into properties and selecting "Don't move or size with the cells" in object positioning. I have also tried locking the aspect ratio of the buttons. But neither have kept the button from resizing. Any ideas of what else I could try?

I've seen this before in older (2003?) Excel. Not sure what version you are using, but if I remember correctly, you might be able to fix this by setting the "Print Object" check box under the Format Control | Properties. Otherwise, you can modify your code to capture the buttons initial dimensions at runtime and force/ensure these dimensions are set before your Exit Sub statement. — David Zemens
– David Zemens, Commented Jul 8, 2013 at 15:49
I am using excel 2010. The print object check box has also been checked, so it sounds like i will probably have to modify the code. thanks — Mike
– Mike, Commented Jul 8, 2013 at 15:54
No problem. Should be easy to do this just get the .Left, .Top, .Height, and .Width of the button, and make sure to reset those at the end of the code. If you have any trouble with it, post your code here and we can try to help! — David Zemens
– David Zemens, Commented Jul 8, 2013 at 16:01
I found the values for height and width, but I am not sure where to locate the .left location and .top location... is this something i need to just play around with in a code or is there a place i can find the current location? — Mike
– Mike, Commented Jul 8, 2013 at 16:40

David Zemens · Accepted Answer · 2013-07-08 16:52:55Z

2

I think this should do the trick.

One other thing I've seen before is that the font size sometimes changes if the button changes. I included this but left it commented out. You may also want/need to reset the button's .TopLeftCell location, which is also included, but commented out in the code below:

'## Add the following to your existing macro:
Dim btn As Shape
Dim btLeft As Double, btTop As Double, btWidth As Double, _
    btHeight As Double, btFontSize As Double
Dim btCell As Range

'## Put this block of code before your existing code:
Set btn = ActiveSheet.Shapes("Button 3") '## Modify to your button name
With btn
    btLeft = .Left
    btTop = .Top
    btHeight = .Height
    btWidth = .Width
    'btFontSize = TextFrame2.TextRange.Font.Size
    'Set btCell = btn.TopLeftCell
End With

'Your code goes here:
'
'
'
'End of your code

'## Put this block of code before the Exit Sub statement/after your existing code
With btn
    '.TopLeftCell = btCell
    '.TextFrame2.TextRange.Font.Size = btnFontSize
    .Left = btLeft
    .Top = btTop
    .Height = btHeight
    .Width = btWidth
End With

answered Jul 8, 2013 at 16:52

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jon Crowell Over a year ago

+1. Answered a similar question a while back, and David's answer above covers option 2 in this answer: stackoverflow.com/a/11532902/138938

David Zemens Over a year ago

+1 @HeadofCatering for some more options. I have personally been using your option 4 most recently, too. Shapes attached to macro seems most reliable, and not prone to this weird behavior.

Collectives™ on Stack Overflow

Buttons resizing in excel-vba on paste

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related