Macro/VBA button help

%3CLINGO-SUB%20id%3D%22lingo-sub-734875%22%20slang%3D%22en-US%22%3EMacro%2FVBA%20button%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-734875%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings!%26nbsp%3B%20I'm%20hoping%20someone%20can%20help%20with%20a%20project%20I'm%20currently%20working%20on.%26nbsp%3B%20I'm%20revamping%20a%20spreadsheet%20for%20work%20that%20we%20use%20to%20track%20hours%20spent%20on%20a%20project.%26nbsp%3B%20I%20have%20a%20table%20that%20contains%20the%20names%20of%20all%20of%20our%20employees%20and%20their%20hours.%20I%20want%20to%20make%20a%20button%20that%20we%20can%20click%20to%20hide%20any%20rows%20containing%20employees%20that%20have%20zero%20hours%20on%20the%20project%20(total%20hours%20are%20in%20column%20I).%26nbsp%3B%20So%20far%2C%20I%20have%20created%20a%20toggle%20button%20using%20the%20activeX%20controls%20(Just%20one%20button%3F%20should%20I%20be%20seeing%20two%3F)%20and%20added%20this%20code%20to%20the%20button%3A%3C%2FP%3E%3CP%3E______%3C%2FP%3E%3CP%3E%3CEM%3EOption%20Explicit%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EPrivate%20Sub%20ToggleButton1_Click()%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EWith%20Me%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20.ToggleButton1.Value%20%3D%20False%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20.AutoFilterMode%20Then%20.UsedRange.AutoFilter%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E.ToggleButton1.Caption%20%3D%20%22Hide%200's%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EElse%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E.UsedRange.Columns(9).AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3D%220%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E.ToggleButton1.Caption%20%3D%20%22Show%200's%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20With%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FP%3E%3CP%3E________%3C%2FP%3E%3CP%3EWhen%20I%20click%20the%20button%20however%2C%20I%20get%20this%20error%3A%3C%2FP%3E%3CP%3E%3CEM%3ERun-time%20error%20'1004'%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EAutoFilter%20method%20of%20Range%20class%20failed%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20a%20bit%20new%20to%20this%20more%20advanced%20level%20excel%20stuff%2C%20so%20my%20technique%20is%20pieced%20together%20from%20various%20things%20I've%20found%20online.%26nbsp%3B%20I%20can't%20seem%20to%20find%20any%20reason%20why%20it%20isn't%20working%2C%20so%20I'm%20hoping%20a%20helpful%20online%20community%20can%20assist%20me%20in%20figuring%20it%20out!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20super%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-734875%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-734939%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%2FVBA%20button%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-734939%22%20slang%3D%22en-US%22%3E%3CP%3E%40Deleted%26nbsp%3B%3C%2FP%3E%3CP%3ESolved!%26nbsp%3B%20A%20kindly%20Reddit%20user%20helped%20me%20out%20and%20thought%20I'd%20share%20it%20here%3A%3C%2FP%3E%3CP%20class%3D%22s1wjcqzz-10%20lkEbBw%22%3ECol%20-%20Column%20where%20you're%20looking%20for%20zero%20values%2C%20%22A%22%20in%20the%20example%3C%2FP%3E%3CP%20class%3D%22s1wjcqzz-10%20lkEbBw%22%3EUpR%20-%20Upper%20range%2C%20last%20row%20of%20data%3C%2FP%3E%3CP%20class%3D%22s1wjcqzz-10%20lkEbBw%22%3ELowR%20-%20Lower%20range%2C%20first%20row%20of%20data%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20ToggleButton1_Click()%0A%0ASet%20WS%20%3D%20Worksheets(%22Sheet1%22)%0ACol%20%3D%20%22A%22%0AUpR%20%3D%2025%0ALowR%20%3D%201%0A%0AIf%20ToggleButton1.Value%20%3D%20True%20Then%0A%20%20%20%20With%20WS%0A%20%20%20%20%20%20%20%20For%20FR%20%3D%20UpR%20To%20LowR%20Step%20-1%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20.Range(Col%20%26amp%3B%20FR)%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Rows(FR).Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20FR%0A%20%20%20%20End%20With%0AEnd%20If%0A%0AIf%20ToggleButton1.Value%20%3D%20False%20Then%0A%20%20%20%20WS.Rows.EntireRow.Hidden%20%3D%20False%0AEnd%20If%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Fc8c0b3%2Fcan_someone_help_with_my_macrovba_button_woes%2F%3Futm_source%3Dshare%26amp%3Butm_medium%3Dios_app%26amp%3Butm_name%3Dios_share_flow_optimization%26amp%3Butm_term%3Denabled%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Fc8c0b3%2Fcan_someone_help_with_my_macrovba_button_woes%2F%3Futm_source%3Dshare%26amp%3Butm_medium%3Dios_app%26amp%3Butm_name%3Dios_share_flow_optimization%26amp%3Butm_term%3Denabled%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

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!

 

1 Reply

@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