Macro Toggle Buttons - Simplify Code to Search Based on Text?

S

sharknado523

New Member
Joined
Jan 10, 2018
Messages
5
Hello guys, I wonder if you can help me. I have a spreadsheet that has three toggle buttons. One is called "Tool," one is called "Fluid" and one is called "Air." The idea is, if I hit "Tool" it hides fluid & air rows for each account. If I hit "Fluid" it hides tool and air, and if I hit "Air" it hides tool and fluid. The code is really long and manual and requires me to make adjustments every time someone adds or loses an account which can be a pain. I wonder if there is a way to get it to just check column A for text of just the words "TOOL" "FLUID" and "AIR" ?

This is my code right now (which has to be adjusted depending on how many accounts the person has):

Code: 
Private Sub ToggleButton1_Click()


If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
    Rows(4).EntireRow.Hidden = True
    Rows(6).EntireRow.Hidden = True
    Rows(8).EntireRow.Hidden = True
    Rows(10).EntireRow.Hidden = True
    Rows(12).EntireRow.Hidden = True
    Rows(14).EntireRow.Hidden = True
    Rows(16).EntireRow.Hidden = True
    Rows(18).EntireRow.Hidden = True
    Rows(20).EntireRow.Hidden = True
    Rows(22).EntireRow.Hidden = True
    Rows(24).EntireRow.Hidden = True
    Rows(26).EntireRow.Hidden = True
    Rows(28).EntireRow.Hidden = True
    Rows(30).EntireRow.Hidden = True
    Rows(32).EntireRow.Hidden = True
    Rows(34).EntireRow.Hidden = True
    Rows(36).EntireRow.Hidden = True
    Rows(38).EntireRow.Hidden = True
    Rows(40).EntireRow.Hidden = True
    Rows(42).EntireRow.Hidden = True
    Rows(44).EntireRow.Hidden = True
    Rows(46).EntireRow.Hidden = True
    Rows(48).EntireRow.Hidden = True
    Rows(50).EntireRow.Hidden = True
    Rows(52).EntireRow.Hidden = True
    Rows(54).EntireRow.Hidden = True
    Rows(58).EntireRow.Hidden = True
    Rows(60).EntireRow.Hidden = True
    Rows(63).EntireRow.Hidden = True
    Rows(65).EntireRow.Hidden = True


Else
'This area contains the things you want to happen
'when the toggle button is depressed
    Rows(4).EntireRow.Hidden = False
    Rows(6).EntireRow.Hidden = False
    Rows(8).EntireRow.Hidden = False
    Rows(10).EntireRow.Hidden = False
    Rows(12).EntireRow.Hidden = False
    Rows(14).EntireRow.Hidden = False
    Rows(16).EntireRow.Hidden = False
    Rows(18).EntireRow.Hidden = False
    Rows(20).EntireRow.Hidden = False
    Rows(22).EntireRow.Hidden = False
    Rows(24).EntireRow.Hidden = False
    Rows(26).EntireRow.Hidden = False
    Rows(28).EntireRow.Hidden = False
    Rows(30).EntireRow.Hidden = False
    Rows(32).EntireRow.Hidden = False
    Rows(34).EntireRow.Hidden = False
    Rows(36).EntireRow.Hidden = False
    Rows(38).EntireRow.Hidden = False
    Rows(40).EntireRow.Hidden = False
    Rows(42).EntireRow.Hidden = False
    Rows(44).EntireRow.Hidden = False
    Rows(46).EntireRow.Hidden = False
    Rows(48).EntireRow.Hidden = False
    Rows(50).EntireRow.Hidden = False
    Rows(52).EntireRow.Hidden = False
    Rows(54).EntireRow.Hidden = False
    Rows(58).EntireRow.Hidden = False
    Rows(60).EntireRow.Hidden = False
    Rows(63).EntireRow.Hidden = False
    Rows(65).EntireRow.Hidden = False
   
End If
End Sub


Private Sub ToggleButton2_Click()


If ToggleButton2.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
    Rows(4).EntireRow.Hidden = True
    Rows(5).EntireRow.Hidden = True
    Rows(8).EntireRow.Hidden = True
    Rows(9).EntireRow.Hidden = True
    Rows(12).EntireRow.Hidden = True
    Rows(13).EntireRow.Hidden = True
    Rows(16).EntireRow.Hidden = True
    Rows(17).EntireRow.Hidden = True
    Rows(20).EntireRow.Hidden = True
    Rows(21).EntireRow.Hidden = True
    Rows(24).EntireRow.Hidden = True
    Rows(25).EntireRow.Hidden = True
    Rows(28).EntireRow.Hidden = True
    Rows(29).EntireRow.Hidden = True
    Rows(32).EntireRow.Hidden = True
    Rows(33).EntireRow.Hidden = True
    Rows(36).EntireRow.Hidden = True
    Rows(37).EntireRow.Hidden = True
    Rows(40).EntireRow.Hidden = True
    Rows(41).EntireRow.Hidden = True
    Rows(44).EntireRow.Hidden = True
    Rows(45).EntireRow.Hidden = True
    Rows(48).EntireRow.Hidden = True
    Rows(49).EntireRow.Hidden = True
    Rows(52).EntireRow.Hidden = True
    Rows(53).EntireRow.Hidden = True
    Rows(58).EntireRow.Hidden = True
    Rows(59).EntireRow.Hidden = True
    Rows(63).EntireRow.Hidden = True
    Rows(64).EntireRow.Hidden = True


Else
'This area contains the things you want to happen
'when the toggle button is depressed
    Rows(4).EntireRow.Hidden = False
    Rows(5).EntireRow.Hidden = False
    Rows(8).EntireRow.Hidden = False
    Rows(9).EntireRow.Hidden = False
    Rows(12).EntireRow.Hidden = False
    Rows(13).EntireRow.Hidden = False
    Rows(16).EntireRow.Hidden = False
    Rows(17).EntireRow.Hidden = False
    Rows(20).EntireRow.Hidden = False
    Rows(21).EntireRow.Hidden = False
    Rows(24).EntireRow.Hidden = False
    Rows(25).EntireRow.Hidden = False
    Rows(28).EntireRow.Hidden = False
    Rows(29).EntireRow.Hidden = False
    Rows(32).EntireRow.Hidden = False
    Rows(33).EntireRow.Hidden = False
    Rows(36).EntireRow.Hidden = False
    Rows(37).EntireRow.Hidden = False
    Rows(40).EntireRow.Hidden = False
    Rows(41).EntireRow.Hidden = False
    Rows(44).EntireRow.Hidden = False
    Rows(45).EntireRow.Hidden = False
    Rows(48).EntireRow.Hidden = False
    Rows(49).EntireRow.Hidden = False
    Rows(52).EntireRow.Hidden = False
    Rows(53).EntireRow.Hidden = False
    Rows(58).EntireRow.Hidden = False
    Rows(59).EntireRow.Hidden = False
    Rows(63).EntireRow.Hidden = False
    Rows(64).EntireRow.Hidden = False
    
End If
End Sub


Private Sub ToggleButton3_Click()


If ToggleButton3.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
    Rows(5).EntireRow.Hidden = True
    Rows(6).EntireRow.Hidden = True
    Rows(9).EntireRow.Hidden = True
    Rows(10).EntireRow.Hidden = True
    Rows(13).EntireRow.Hidden = True
    Rows(14).EntireRow.Hidden = True
    Rows(17).EntireRow.Hidden = True
    Rows(18).EntireRow.Hidden = True
    Rows(21).EntireRow.Hidden = True
    Rows(22).EntireRow.Hidden = True
    Rows(25).EntireRow.Hidden = True
    Rows(26).EntireRow.Hidden = True
    Rows(29).EntireRow.Hidden = True
    Rows(30).EntireRow.Hidden = True
    Rows(33).EntireRow.Hidden = True
    Rows(34).EntireRow.Hidden = True
    Rows(37).EntireRow.Hidden = True
    Rows(38).EntireRow.Hidden = True
    Rows(41).EntireRow.Hidden = True
    Rows(42).EntireRow.Hidden = True
    Rows(45).EntireRow.Hidden = True
    Rows(46).EntireRow.Hidden = True
    Rows(49).EntireRow.Hidden = True
    Rows(50).EntireRow.Hidden = True
    Rows(53).EntireRow.Hidden = True
    Rows(54).EntireRow.Hidden = True
    Rows(59).EntireRow.Hidden = True
    Rows(60).EntireRow.Hidden = True
    Rows(64).EntireRow.Hidden = True
    Rows(65).EntireRow.Hidden = True


Else
'This area contains the things you want to happen
'when the toggle button is depressed
    Rows(5).EntireRow.Hidden = False
    Rows(6).EntireRow.Hidden = False
    Rows(9).EntireRow.Hidden = False
    Rows(10).EntireRow.Hidden = False
    Rows(13).EntireRow.Hidden = False
    Rows(14).EntireRow.Hidden = False
    Rows(17).EntireRow.Hidden = False
    Rows(18).EntireRow.Hidden = False
    Rows(21).EntireRow.Hidden = False
    Rows(22).EntireRow.Hidden = False
    Rows(25).EntireRow.Hidden = False
    Rows(26).EntireRow.Hidden = False
    Rows(29).EntireRow.Hidden = False
    Rows(30).EntireRow.Hidden = False
    Rows(33).EntireRow.Hidden = False
    Rows(34).EntireRow.Hidden = False
    Rows(37).EntireRow.Hidden = False
    Rows(38).EntireRow.Hidden = False
    Rows(41).EntireRow.Hidden = False
    Rows(42).EntireRow.Hidden = False
    Rows(45).EntireRow.Hidden = False
    Rows(46).EntireRow.Hidden = False
    Rows(49).EntireRow.Hidden = False
    Rows(50).EntireRow.Hidden = False
    Rows(53).EntireRow.Hidden = False
    Rows(54).EntireRow.Hidden = False
    Rows(59).EntireRow.Hidden = False
    Rows(60).EntireRow.Hidden = False
    Rows(64).EntireRow.Hidden = False
    Rows(65).EntireRow.Hidden = False


End If
End Sub

It should be noted it has to be exact matches only because for example if a company is called "NORTHERN TOOL" it can't hide that line, it has to be only "Tool" "Fluid" and "Air" that are affected.

You can view a photo of a snippet of my sheet here (company names removed).

https://www.dropbox.com/s/iexqmys4nd3ayfw/MrExcel Screenshot.png?dl=0

Any help would be HUGELY appreciated - we've been using this sheet for a year and the macro works fine but it would be a lot easier to make updates throughout the year to account ownership if the macro were simplified.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
"I wonder if there is a way to get it to just check column A for text of just the words "TOOL" "FLUID" and "AIR" ?"
Sure. I don't really understand what U want to do after U have found the word? HTH. Dave
Code: 
Sub test()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
If Sheets("Sheet1").Range("A" & Cnt).Value <> vbNullString Then
If CStr(Sheets("Sheet1").Range("A" & Cnt).Value) = "TOOL" Then
'do stuff for "TOOL" here
MsgBox "TOOL on row: " & Cnt
End If
If CStr(Sheets("Sheet1").Range("A" & Cnt).Value) = "FLUID" Then
'do stuff for "FLUID" here
MsgBox "FLUID on row: " & Cnt
End If
If CStr(Sheets("Sheet1").Range("A" & Cnt).Value) = "AIR" Then
'do stuff for "AIR here
MsgBox "AIR on row: " & Cnt
End If
End If
Next Cnt
End Sub
 
Upvote 0
@ sharknado523, first of all with your question what is the issue with using the built in Autofilter for doing the task?

Btw, a couple of remarks about the code you posted...

Rows already refers to the entire row so you don't need to use EntireRow i.e.
Code: 
Rows(4).EntireRow.Hidden = True
would be
Code: 
Rows(4).Hidden = True

Or to shorten the code you could use Range so
Code: 
    Rows(4).EntireRow.Hidden = True
    Rows(6).EntireRow.Hidden = True
    Rows(8).EntireRow.Hidden = True
    Rows(10).EntireRow.Hidden = True
    Rows(12).EntireRow.Hidden = True
    Rows(14).EntireRow.Hidden = True
    Rows(16).EntireRow.Hidden = True

could be written as
Code: 
Sub xxx()
    Range("4:4,6:6,8:8,10:10,12:12,14:14,16:16").EntireRow.Hidden = True
End Sub

If it wasn't for a couple of changes to the pattern at the end of your blocks you could just loop through the alternate rows.
 
Last edited:
Upvote 0
NdNoviceHlp said:
"I wonder if there is a way to get it to just check column A for text of just the words "TOOL" "FLUID" and "AIR" ?"
Sure. I don't really understand what U want to do after U have found the word? HTH. Dave
Click to expand...

Sorry, to clarify - the way it works now is when you hit "tool" it hides fluid and air. When you hit "fluid" it hides tool and air. When you hit "air" it hides tool and fluid. It gives people the ability to go into "Tool Mode" or "Air Mode" depending on what they're tracking.
 
Upvote 0
You could use something like this.
Code: 
Private Sub ToggleButton1_Click()
    If ToggleButton1.Value Then
        ToggleButton2.Value = False
        ToggleButton3.Value = False
        ShowRowsWithWord "TOOL"
    Else
        ShowRowsWithWord "*"
    End If
End Sub

Private Sub ToggleButton2_Click()
    If ToggleButton2.Value Then
        ToggleButton1.Value = False
        ToggleButton3.Value = False
        ShowRowsWithWord "AIR"
    Else
        ShowRowsWithWord "*"
    End If
End Sub

Private Sub ToggleButton3_Click()
    If ToggleButton3.Value Then
        ToggleButton1.Value = False
        ToggleButton2.Value = False
        ShowRowsWithWord "FLUID"
    Else
        ShowRowsWithWord "*"
    End If
End Sub

Sub ShowRowsWithWord(ShowWord As String)
    Dim HIdeRows As Range, ShowRows As Range
    Dim oneCell As Range
    
    ShowWord = LCase(ShowWord)
    
    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            Set HIdeRows = .Cells(.Rows.Count + 1, 1)
            
            If ShowWord = "*" Then
                Set ShowRows = .Cells
            Else
            
                Set ShowRows = HIdeRows
                For Each oneCell In .Cells
                    Select Case LCase(CStr(oneCell.Value))
                        Case ShowWord
                            Set ShowRows = Application.Union(ShowRows, oneCell)
                        Case "tool", "fluid", "air"
                            Set HIdeRows = Application.Union(HIdeRows, oneCell)
                        Case Else
                            Rem do nothing
                    End Select
                Next oneCell
                
            End If
            
            On Error Resume Next
            ShowRows.EntireRow.Hidden = False
            Application.Intersect(.Cells, HIdeRows).EntireRow.Hidden = True
            On Error GoTo 0
            
        End With
    End With
    
End Sub
Since you are using ToggleButtons rather than option buttons, you didn't mention what you want to do if two buttons are toggled. This code automatically clears the buttons of the hidden rows. It also ignores any row whose column A cells doesn't have one of the three keyterms.

But, I'd add my voice to those recommending the use of the built in AutoFilter rather than your own macros.
 
Last edited:
Upvote 0
Yeah so guys after taking a fresh look at this sheet after a year of its existence I ended up realizing that by tweaking the design I could totally just make a filtered list. Now it's way easier to view two categories at the same time, the document is much smaller (no macro codes all over the sheet for stupid buttons) and I don't have to worry about the buttons randomly resizing when I project my screen because the ActiveX controls for the button are buggy.

Thanks to you guys for the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Hiding rows with multiple dropdown menu's
Replies
6
Views
978
kevin9999
K
R
  • Question
Hide/Unhide Rows on different sheets based on cell value from one main sheet
2
Replies
15
Views
3K
Ryle23
R
M
  • Solved
how to combine two vba code?
Replies
4
Views
604
mychi11
M
M
  • Question
VBA Code to hide cells based upon cell on another sheet
Replies
3
Views
581
Yongle
Yongle
H
  • Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
443
GraH
GraH

Forum statistics

Threads
1,216,309
Messages
6,130,001
Members
449,551
Latest member
MJS_53

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top