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):
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.
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.