Toggle button in Excel not actioning, but name is updating

Question

I have two toggle buttons, one to hide some rows (tagged in column B with 2) and another to hide other rows (tagged in column B with 3) The button was working last week, but this morning it's stopped.

The toggle buttons update their name (i.e. "hide" becomes "show") but the rows themselves stay unhidden.

I am very inexperienced with VBA - this code was cribbed from various excel help online. So, it'd be a huge favour if any help is explained in simple and detailed steps.

Thanks in advance!

Private Sub ToggleButton1_Click()
        Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Interim HSAP") ' Change to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For i = 8 To lastRow
        If ws.Cells(i, "B").Value = 2 Then
            ws.Rows(i).Hidden = ToggleButton1.Value
        End If
    Next i

    ' Update the button caption
    If ToggleButton1.Value Then
        ToggleButton1.Caption = "Show Milestones"
    Else
        ToggleButton1.Caption = "Hide Milestones"
    End If
End Sub

Private Sub ToggleButton2_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Interim HSAP") ' Change to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For i = 8 To lastRow
        If ws.Cells(i, "B").Value = 3 Then
            ws.Rows(i).Hidden = ToggleButton2.Value
        End If
    Next i

    ' Update the button caption
    If ToggleButton2.Value Then
        ToggleButton2.Caption = "Show Actions"
    Else
        ToggleButton2.Caption = "Hide Actions"
    End If
End Sub

I have tried going back to a previous version of the spreadsheet when I first installed the buttons, and they work with similar code. (It was before I made a bunch of changes to get the exact behaviour that I wanted, but as far as I can see, they are very similar)

Answer 1

Your code works OK for me, so likely you've changed something and your sheet setup no longer matches your code.

Here's a streamlined version which puts the common logic into a separate method.

Option Explicit

Private Sub ToggleButton1_Click()
    HideShowRows ToggleButton1, 2, "Milestones"
End Sub

Private Sub ToggleButton2_Click()
    HideShowRows ToggleButton2, 3, "Actions"
End Sub


Sub HideShowRows(tb As ToggleButton, checkVal, lbl As String)
    Const CHECK_COL As String = "B"  'col with values to be matched
    Dim i As Long
    
    'check the rows....  `Me` = the worksheet for the code module
    For i = 8 To Me.Cells(Me.Rows.Count, CHECK_COL).End(xlUp).Row
        If Me.Cells(i, CHECK_COL).Value = checkVal Then
            Me.Rows(i).Hidden = tb.Value
        End If
    Next i
    'update the caption
    tb.Caption = IIf(tb.Value, "Show", "Hide") & " " & lbl
End Sub