0

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?

Mike
  • 269
  • 6
  • 10
  • 18
  • 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 Jul 08 '13 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 Jul 08 '13 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 Jul 08 '13 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 Jul 08 '13 at 16:40
  • Check my answer below, I think that should take care of it. – David Zemens Jul 08 '13 at 16:53

1 Answers1

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
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 1
    +1. Answered a similar question a while back, and David's answer above covers option 2 in this answer: http://stackoverflow.com/a/11532902/138938 – Jon Crowell Jul 08 '13 at 17:11
  • 1
    +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. – David Zemens Jul 08 '13 at 17:32