Buttons often expand to fill the worksheet. Button Bug

[Prity]

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]

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]

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]

Nice.