Home

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
NikkiBlast
New Contributor

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
Highlighted

@NikkiBlast 

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_so...

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies