ActiveX check box to hide rows between two rows with specific text

Question

I want to use a check box to hide all rows between two rows that have the text "XXX" and "YYY". First I am defining and setting public ranges to the cells containing the text with this Sub:

Public Sub Workbook_Open()

Public D1 As Range, D2 As Range
Set D1 = Sheet3.Columns("A").Find(what:="XXX", LookIn:=xlValues, lookat:=xlWhole)
Set D2 = Sheet3.Columns("A").Find(what:="YYY", LookIn:=xlValues, lookat:=xlWhole)

End Sub

I then set up the activeX check box like this:

Private Sub Check_Click()

If Check = True Then
Sheet3.Rows(D1.Row + 1 & ":" & D2.Row - 2).Hidden = False
Else:
Sheet3.Rows(D1.Row + 1 & ":" & D2.Row - 2).Hidden = True
End If

End Sub

However, I keep getting the following error: Object required. Am I declaring the Ranges wrong? How can I fix this? Thanks.

Answer 1

Further to my comments below your post, try something like this (UNTESTED)

Private Sub Check_Click()
    Dim D1 As Range, D2 As Range

    With Sheet3
        Set D1 = .Columns(1).Find(what:="XXX", LookIn:=xlValues, lookat:=xlWhole)
        Set D2 = .Columns(1).Find(what:="YYY", LookIn:=xlValues, lookat:=xlWhole)

        If D1 Is Nothing Then Exit Sub
        If D2 Is Nothing Then Exit Sub

        If Check = True Then
            .Rows(D1.Row + 1 & ":" & D2.Row - 2).Hidden = False
        Else
            .Rows(D1.Row + 1 & ":" & D2.Row - 2).Hidden = True
        End If
    End With
End Sub

I am assuming the following

1. You are using an ActiveX Checkbox called Check
2. You have a sheet which has a codename Sheet3