Buttons moving, resizing, when locked.

[showboat098]

I have a bit of a mystery in my spreadsheet. I have a series of buttons that filter a pivot table according to the text of the button. These buttons sit on top of shapes. Each button has the "Don't Move or size with cells" option select, and each is locked. As the pivot table is used, saved, zoomed in and out the buttons resize. The buttons were created initially by creating one and copying it numerous times. Any suggestions as to how to fix this? We are currently considering changing the buttons to shapes, but the obvious downside is that changes the asthetic.

 

[pbornemeier]

In an Excel 2003 file I had a similar problem that I "solved" by recreating the button set on a new worksheet. In a new workbook. As an interim measure I had written a procedure to recreate the buttons in there desired position/size and triggered it with the worksheet activate event. You could triger it with the PivotTableUpdate event.

I did not need the repositioning procedure in the new workbook. I never did figure out what triggered the sbutton size to randomly change.

 

[showboat098]

This particular spreadsheet was created as 2003 spreadsheet, but then converted to 2010 soon after initial layout. The recreation solution sounds intriguing as a potential bandaid solution, but I have to admit I'm not familiar with the code required. I have just recently mad this spreadsheet available to my broader firm, and as I'm maintaining it the buttons are slowly changing shape/size.

 

[pbornemeier]

This is the type of code to use on the codepage of the worksheet that contains the PivotTable:

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    ActiveSheet.Shapes("Button 1").Select
    Selection.ShapeRange.Height = 39#
    Selection.ShapeRange.Width = 144#
    Selection.Top = 55
    Selection.Left = 127

End Sub

You can either use the default button names or give the button descriptive names (cmdFilter or something) and use those.

Alos add the code to the sheet activate event so that the buttons are set whenever the sheet is viewed

Private Sub Worksheet_Activate()
    ActiveSheet.Shapes("Button 1").Select
    Selection.ShapeRange.Height = 39#
    Selection.ShapeRange.Width = 144#
    Selection.Top = 55
    Selection.Left = 127
End Sub