Macro Button Moving & Staying Collapsed When Rows Are "Un-Collapsed"

[ohnow]

Hello;
I haven't been able to find this answer yet so here goes. . . For years I've noticed if you paste a picture onto an Excel sheet or create a macro button, and you change the properties to move and size with rows, and you group the rows, and you collapse the rows, when you "un-collapse" the rows the picture or button "object" would still stay collapsed unless you make sure there are 1-2 rows above and below the start and end of the rows that are grouped.

After I learned the extra rows trick, it has worked fine. I now have a file where I created a new sheet and performed the steps above inserting several macro buttons. It worked as expected. . . until I saved, closed and re-opened the file. At that point all buttons within the group of rows that had been collapsed still stay collapsed (you only see a straight line the width of the button) and all buttons are moved to the first row that begins the grouping.

I have fixed and saved, changed properties and still the same behavior. Besides making a whole new file, are there any fixes for this?

Thanks in advance!

 

[SpillerBD]

Just don't do it. I have never felt the Sheet was great place for macro buttons, and if it is the necessary place then place those buttons in areas that aren't to be resized or (worse) hidden.
My QAT is where I have most, and then I use custom ribbons for others.

Of course if you are using Excel 4.0 grouping instead of better tools like Pivot Tables... (Yeah, I hate Outlining Grouping. It defeats and interferes with many other tools for analysis.)

 

[JoeMo]

You could use a workbook_beforeOpen event macro to restore the buttons to your desired size. For example, the event macro could call a macro like the one below where I've just assigned some arbitrary location and sizing:

Sub ButtonRestoreSize()
Dim btn As Shape, ct As Long
For Each btn In ActiveSheet.Shapes
    If btn.Name Like "*Button*" Then
        ct = ct + 1
        With btn
            .Left = Range("G5").Offset(3 * ct, 0).Left
            .Top = Range("G5").Offset(3 * ct).Top
            .Height = Range("G5").Height * 2
            .Width = Range("G5").Width * 2
        End With
    End If
Next btn
End Sub

EDIT: This assumes your command buttons are forms controls, not active-x.

 

[ohnow]

Hello;
I haven't been able to ...

Thank you for your help!