VBA - ToggleButton Hide/Unhide Rows

[saechu]

I'm new to VBA and am trying to figure out a way to do the following.
I have 3 spreadsheets (Live, Control, Compare). Live & Control have a list of names in D8:D99 and Compare has a list in A7:A98 with a number field in B7:B98.
I want to create a toggle button that will hide rows based on the criteria below (as well as unhide all rows if clicked again).
- If value in B is 0 AND
- Name in Compare is on both Live & Control lists

If the name in B7:B98 is missing from either Live or Control list or the value > 0, I do not want to hide the row.

Any help would be greatly appreciated. Thanks!

 

[Caleeco]

Hi Saechu,

The two macros below should achieve what you want.

Macro1: To Hide rows

Sub HideRows()
    Dim wsComp As Worksheet
    Dim searchRNG1 As Range, searchRNG2 As Range, c As Range, d As Range
    Dim i As Long, val As Long
    Dim Name As Variant
    
    Set wsComp = Sheets("Compare")
    Set searchRNG1 = Sheets("Live").Range("D8:D99")
    Set searchRNG2 = Sheets("Control").Range("D8:D99")
    
    For i = 98 To 7 Step -1
        val = wsComp.Range("B" & i).Value
        If val = 0 Then
            Set c = searchRNG1.Find(val, , xlValues, xlPart, xlByRows)
            If Not c Is Nothing Then
                Set d = searchRNG2.Find(val, , xlValues, xlPart, xlByRows)
                If Not d Is Nothing Then
                    wsComp.Range("B" & i).EntireRow.Hidden = True
                End If
            End If
        End If
    Next i
End Sub

Macro2: To unhide the rows:

Sub UnhideRows()
    Sheets("Compare").Range("B7:B98").EntireRow.Hidden = False
End Sub

For the toggle, I would link it to a Shape name.. so if your shape name is "On" run macro1, if the Shape name is off run Macro2 etc.

If you need help doing this let me know
Thanks
Caleeco