PDA

View Full Version : Buttons often expand to fill the worksheet. Button Bug



Prity
09-30-2014, 12:36 AM
Hi All,

In my excel program, i have some buttons placed on it.also it has some sections.

Buttons often expand to fill the worksheet. This tends to happen when sections are hidden. Either find and fix it or remove the ability to hide sections.

Buttons size changes or buttons stop working. Happens when the excel workbook is used through two screens having different resolutions.Proper instuctions can be added for the user to prevent such issues.

How to resolve this issue.

Thanks and Regards
Prity

ranman256
09-30-2014, 01:41 PM
you could run a macro that resets the button sizes.
I put msgbox, but you can change it to assign: s.height = 88



Sub SetButtonSize()
Dim s As Shape
For Each s In ActiveSheet.Shapes
If s.Type = 8 Then
MsgBox s.Name, , "button"
MsgBox s.Height, , s.Name
MsgBox s.Width, , s.Name
End If
Next
Set s = Nothing
End Sub

jolivanes
09-30-2014, 11:20 PM
Put this code in a regular module (or in your Personal.xlsb for future use)

Sub Dont_Move_Or_Resize()
With ActiveSheet.Shapes
With Selection
.Placement = xlFreeFloating
.PrintObject = False
End With
End With
End Sub
Select all the buttons and select (in 2007) "Developer" - "Macro", select "Dont_Move_Or_Resize" - "Run"




Or right click on the Button, Format Control, Properties and select the "Don't move or size with cells" radio button and click on OK.

ranman256
10-01-2014, 08:10 AM
Nice.