Excel control button sometimes expands to huge size

mmingfeilam
mmingfeilam used Ask the Experts™
on
i have an Excel file that is related to my VSTO 3.0 project.  sometimes when i open the Excel file, the button gets to be huge, and i get "Not enough storage is available to process this command" error.  is there a reason why it happens sometimes?  thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Command button on a worksheet?

I've not seen them grow, but I have a couple of worksheets where they shrink periodically so I wrote the following code to size the buttons on open.

This is located in "This Workbook"
Private Sub Workbook_Open()
    FixButtons 'resize buttons
End Sub

Sub FixButtons()
    'sheet selection
    ActiveSheet.Shapes.Range(Array("CommandButton5")).Width = 10
    ActiveSheet.Shapes.Range(Array("CommandButton5")).Width = 92.25
    ActiveSheet.Shapes.Range(Array("CommandButton5")).Height = 21.75
    ActiveSheet.Shapes.Range(Array("CommandButton5")).Top = 3.75
    ActiveSheet.Shapes.Range(Array("CommandButton5")).Left = 10.5
    
    'update summary
    ActiveSheet.Shapes.Range(Array("CommandButton2")).Width = 10
    ActiveSheet.Shapes.Range(Array("CommandButton2")).Width = 92.25
    ActiveSheet.Shapes.Range(Array("CommandButton2")).Height = 21.75
    ActiveSheet.Shapes.Range(Array("CommandButton2")).Top = 3.75
    ActiveSheet.Shapes.Range(Array("CommandButton2")).Left = 105.75
    
    'add
    ActiveSheet.Shapes.Range(Array("CommandButton3")).Width = 10
    ActiveSheet.Shapes.Range(Array("CommandButton3")).Width = 92.25
    ActiveSheet.Shapes.Range(Array("CommandButton3")).Height = 21.75
    ActiveSheet.Shapes.Range(Array("CommandButton3")).Top = 3.75
    ActiveSheet.Shapes.Range(Array("CommandButton3")).Left = 201
    
    'copy row(s)
    ActiveSheet.Shapes.Range(Array("CommandButton4")).Width = 10
    ActiveSheet.Shapes.Range(Array("CommandButton4")).Width = 92.25
    ActiveSheet.Shapes.Range(Array("CommandButton4")).Height = 21.75
    ActiveSheet.Shapes.Range(Array("CommandButton4")).Top = 3.75
    ActiveSheet.Shapes.Range(Array("CommandButton4")).Left = 296.25
End Sub

Open in new window


HTH

Scot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial