CODEWITHSUNDEEP
excelvbatoggle
Anna Maxted
Anna Maxted

Reputation: 1

Change module - one click to toggle button vba excel

I have the following code in a module in Excel, however i'd like to assign it to a toggle button.

Sub Weeks3Hide()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Weekly Hoist Hours")

wsLR = ws.Cells(Rows.Count, 7).End(xlUp).Row

For x = 14 To wsLR
    'analyze date, see if it's 3 weeks or older
    If ws.Cells(x, 7) <= Date - 21 Then
        'hide
        ws.Range("a" & x).EntireRow.Hidden = True
    End If
Next x

End Sub

I've so far got this: However there is an error in it.

Private Sub ToggleButton4_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Weekly Hoist Hours")

WsLR = ws.Cells(Rows.Count, 7).End(xlUp).Row
If ToggleButton4.Value = True Then
For x = 14 To WsLR
    'analyze date, see if it's 3 weeks or older
    If ws.Cells(x, 7) <= Date - 21 Then
        'hide
        ws.Range("a" & x).EntireRow.Hidden = True
    End If
Next x
Else
  ws.Range(WsLR).EntireRow.Hidden = False
End If
End Sub

Would appreciate any help! Thanks in advance

Upvotes: 0

Views: 394

Answers (2)

TinMan
TinMan

Reputation: 7759

The first thing to do is unhide any hidden rows. I would then iterate over the cells and use Union to create a Range of target Rows. In this way, you can hide all the Rows at once. 

Private Sub ToggleButton4_Click()
    Dim cell As Range, Rows As Range

    With ThisWorkbook.Sheets("Weekly Hoist Hours")
        For Each cell In .Range(Cells(14, 7), .Cells(Rows.Count, 7).End(xlUp))
            .Rows.Hidden = False
            If Not .ToggleButton1.Value Then Exit Sub

            If cell.Value <= Date - 21 Then
                If Rows Is Nothing Then
                    Set Rows = cell.EntireRow
                Else
                    Set Rows = Union(Rows, cell.EntireRow)
                End If
            End If
        Next
        Rows.Hidden = False
    End With

End Sub

Upvotes: 0

Michal
Michal

Reputation: 5813

This line ws.Range(WsLR).EntireRow.Hidden = False.

You cannot refer to range simply with a number. If you are trying to hide a row you can use ws.Rows(WsLR).EntireRow.Hidden = False or something like ws.Rows("A2:A" & WsLR).EntireRow.Hidden = False.

Upvotes: 1

Related Questions