Toggle Button to Hide/Unhide column based on specific text in rows

[onthegreen03]

Hi All -

I want to add a Toggle Button, that when clicked will search all rows in column C of my spreadsheet and hide the rows which contain the word Vacant. Obviously, when the button is clicked again it will unhide all rows that contain the word Vacant. Can anyone send me the VBA code that will get this done? I've looked around on line and even tried a few times and can't seem to figure it out. Thanks in advance!

 

[My Aswer Is This]

Try this:

Private Sub ToggleButton1_Click()
'Modified 6/29/2022  12/31/1899 12:01:46 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 To Lastrow
    If Cells(i, 3).Value = "Vacant" Then Rows(i).Hidden = Not Rows(i).Hidden
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

This script will run the same if it's in a Toggle Button or a Command button

 

[onthegreen03]

This works like a charm, thank you! Would you be able to modify the code slightly so I can add captions when hidden and unhidden? When hidden I would like the button to read "Unhide Vacant", and when unhidden I would like "Hide Vacant". Thanks again!

 

[My Aswer Is This]

This works like a charm, thank you! Would you be able to modify the code slightly so I can add captions when hidden and unhidden? When hidden I would like the button to read "Unhide Vacant", and when unhidden I would like "Hide Vacant". Thanks again!

This script runs through a range of cells to see if they equal "Vacant" or not.
So, if we are checking 100 rows to see if the value= "Vacant" then in one row it would be true but the next row it may not be true
So that would mean the Caption on the Toggle Button would be going from Hidden to Not Hidden maybe 50 times almost instantly back and forth. Not sure why you would need that. It's not like checking one cell

 

[My Aswer Is This]

Here is an example of how A Toggle Button works.

Private Sub ToggleButton1_Click()
'Modified  6/30/2022  12:53:14 PM  EDT
If ToggleButton1.Value = True Then Range("A1").Value = "Alpha": ToggleButton1.Caption = "Alpha"
If ToggleButton1.Value = False Then Range("A1").Value = "Bravo": ToggleButton1.Caption = "Bravo"
End Sub

 

[onthegreen03]

I was trying to add the same functionality as my other toggle buttons on the sheet (see the code below which I am using to hide/unhide certain columns). I like that the caption automatically changes depending on if the button is pressed or not. No worries, I can just skip it. Thanks again!

Private Sub ToggleButton1_Click()
Dim xAddress As String
xAddress = "G:K"
If ToggleButton1.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    ToggleButton1.Caption = "Show Q1"
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    ToggleButton1.Caption = "Hide Q1"
End If
End Sub

 

[My Aswer Is This]

OK I have figured out how to do this:
Using A Toggle Button:
Try this:

Private Sub ToggleButton1_Click()
'Modified  6/30/2022  1:39:11 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    
    If ToggleButton1.Value = True Then
        ToggleButton1.Caption = "Hidden"
            For i = 1 To Lastrow
                If Cells(i, 3).Value = "Vacant" Then Rows(i).Hidden = True
            Next
    End If


    If ToggleButton1.Value = False Then
        ToggleButton1.Caption = "Not Hidden"
        For i = 1 To Lastrow
            If Cells(i, 3).Value = "Vacant" Then Rows(i).Hidden = False
        Next
    End If
Application.ScreenUpdating = True
End Sub

 

[onthegreen03]

You nailed it, thanks again for the help!

 

[My Aswer Is This]

You nailed it, thanks again for the help!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.