Excel 2010 command button disappears

Question

I'm developing an Excel 2010 workbook, in a manual formulas calculation mode.

(file -> options -> formulas -> Workbook calculation -> manual)

I have some command buttons in the sheet (ActiveX controls), and I set them to move and size with cells (right click on the button -> format control -> Properties -> move and size with text).

This is since I have some rows filtered out under some conditions, and I want the buttons placed in these rows to appear and disappear as well, according to the display mode of their hosting rows.

It all goes perfectly fine, till I save he worksheet when some of the rows (hence buttons) are filtered out (i.e. not displayed).

When I re-open the file again, and expand the filtered rows, the buttons don't show. When checking their properties I see that their visible property is True, but their height is 0, and this doesn't change when I un-filter their hosting rows.

I want to emphasize again that before saving the file - both filtering and un-filtering the buttons worked well.

Would much appreciate any help here.

Answer 1

OK so I get the same results either with ActiveX or Form Controls. For whatever reason, it seems the control's original height does not persist beyond the save & close.

Another option would be to simply clear the AutoFilter on the Workbook's Close and Save events. However, this probably is not what you want if you like to leave some filter(s) on when you save and re-open the file. It's probably possible to save the filter parameters in a hidden sheet or by direct manipulation of the VBE/VBA, but that seems like a LOT more trouble than it's worth. Then you could re-apply the filter(s) when you re-open the workbook.

Here is what code I suggest

NOTE: I relied on the worksheet's _Calculate event with a hidden CountA formula (setting, changing, or clearing the AutoFilter will trigger this event). I put the formula in E1 just so you can see what it looks like:

Since your application relies on Calculation = xlManual then this approach will not work exactly for you but in any case, the subroutine UpdateButtons could be re-used. You would need to tie it in to another event(s) or functions in your application, as needed.

Here is the code

Option Explicit
Private Sub UpdateButtons()
'## Assumes one button/shape in each row
'   buttons are named/indexed correctly and
'   the first button appears in A2
Dim rng As Range
Dim shp As Shape
Dim i As Long

Application.EnableEvents = False
'## use this to define the range of your filtered table
Set rng = Range("A1:A6")

'## Iterate the cells, I figure maybe do this backwards but not sure
'   if that would really make a difference.
For i = rng.Rows.Count To 2 Step -1
    Set shp = Nothing
    On Error Resume Next
    Set shp = Me.Shapes(i - 1)
    On Error GoTo 0

    If Not shp Is Nothing Then
        DisplayButton Me.Shapes(i - 1), Range("A" & i)
    End If
Next

Application.EnableEvents = True
End Sub

Private Sub DisplayButton(shp As Shape, r As Range)
    '# This subroutine manipulates the shape's size & location
    shp.Top = r.Top
    shp.TopLeftCell = r.Address
    shp.Height = r.Height
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "_Change"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''## Assumes one button/shape in each row
''   buttons are named/indexed correctly and
''   the first button appears in A2
'Dim rng As Range
'Dim shp As Shape
'Dim i As Long
'
''## Uncomment this line if you want an annoying message every time
''MsgBox "Refreshing Command Buttons!"
'
'Application.EnableEvents = False
''## use this to define the range of your filtered table
'Set rng = Range("A1:A6")
'
''## Iterate the cells, I figure maybe do this backwards but not sure
''   if that would really make a difference.
'For i = rng.Rows.Count To 2 Step -1
'    Set shp = Nothing
'    On Error Resume Next
'    Set shp = Me.Shapes(i - 1)
'    On Error GoTo 0
'
'    If Not shp Is Nothing Then
'        DisplayButton Me.Shapes(i - 1), Range("A" & i)
'    End If
'Next
'
'Application.EnableEvents = True

End Sub

For Another option See this article. You can re-purpose existing commands with RibbonXML customization. While this article is geared towards C# and Visual Studio it's possible to do it with the CustomUI Editor.

Answer 2

I had a similar problem with buttons disapearing (moving on upper left corner) when removing filters.

A solution I found was to add a row above the columns headers so that buttons were still appearing at the top of the columns but were not touching the row where filters were placed.

Adding / removing filters stop interfering with buttons' positions.

Answer 3

I had a similar problem where form buttons appear to work fine, but then disappear after saving and reopening the workbook. Specifically this happened when the form button where part of hidden rows (done using vba code).

Seems like a real bug, although I don't know where the link is.

By changing the form buttons to ActiveX buttons, the buttons stopped disappearing, but started moving/bunching to the top of the screen when the rows were hidden. I just added some vba to re-position the buttons (e.g. CommandButton1.Top = Range(A12:A12).Top --> moves the ActiveX command button to the 12th row).