Forum Discussion

Deleted's avatar
Deleted
Jul 02, 2019

Macro/VBA button help

Greetings!  I'm hoping someone can help with a project I'm currently working on.  I'm revamping a spreadsheet for work that we use to track hours spent on a project.  I have a table that contains the names of all of our employees and their hours. I want to make a button that we can click to hide any rows containing employees that have zero hours on the project (total hours are in column I).  So far, I have created a toggle button using the activeX controls (Just one button? should I be seeing two?) and added this code to the button:

______

Option Explicit

Private Sub ToggleButton1_Click()
With Me
If .ToggleButton1.Value = False Then
If .AutoFilterMode Then .UsedRange.AutoFilter
.ToggleButton1.Caption = "Hide 0's"
Else
.UsedRange.Columns(9).AutoFilter Field:=1, Criteria1:="0"
.ToggleButton1.Caption = "Show 0's"
End If
End With
End Sub

________

When I click the button however, I get this error:

Run-time error '1004':

AutoFilter method of Range class failed

 

I'm a bit new to this more advanced level excel stuff, so my technique is pieced together from various things I've found online.  I can't seem to find any reason why it isn't working, so I'm hoping a helpful online community can assist me in figuring it out!

 

Any advice would be super appreciated!

 

excel
Macros and VBA

1 Reply

  • Deleted's avatar
    Deleted
    Jul 02, 2019

    Deleted 

    Solved!  A kindly Reddit user helped me out and thought I'd share it here:

    Col - Column where you're looking for zero values, "A" in the example

    UpR - Upper range, last row of data

    LowR - Lower range, first row of data

    Private Sub ToggleButton1_Click()

Set WS = Worksheets("Sheet1")
Col = "A"
UpR = 25
LowR = 1

If ToggleButton1.Value = True Then
    With WS
        For FR = UpR To LowR Step -1
            If .Range(Col & FR) = 0 Then
                .Rows(FR).Hidden = True
            End If
        Next FR
    End With
End If

If ToggleButton1.Value = False Then
    WS.Rows.EntireRow.Hidden = False
End If

End Sub

     https://www.reddit.com/r/excel/comments/c8c0b3/can_someone_help_with_my_macrovba_button_woes/?utm_source=share&utm_medium=ios_app&utm_name=ios_share_flow_optimization&utm_term=enabled

Resources