Buttons moving, resizing, when locked.

showboat098

New Member
Joined
Dec 3, 2012
Messages
17
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
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

New Member
Joined
Dec 3, 2012
Messages
17
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

Well-known Member
Joined
May 24, 2005
Messages
3,882
This is the type of code to use on the codepage of the worksheet that contains the PivotTable:
Code:
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top