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
Highlighted
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

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies