Checkbox to hide or unhode rows on a different sheet based on cell value

[sdoppke]

Hello everyone, I am looking to place a checkbox on sheet1 that will check rows 1-2000 on sheet2 for the value in A1 of sheet1 and hide or unhide those rows based on if its checked or not.

thanks a ton in advacne for any direction.

sd

 

[sdoppke]

Hello everyone, I am looking to place a checkbox on sheet1 that will check rows 1-2000 on sheet2 for the value in A1 of sheet1 and hide or unhide those rows based on if its checked or not.

thanks a ton in advacne for any direction.

sd

I have managed to put this together, but its doesnt work

Anyone have any ideas?

sd

Private Sub CheckBox1_Click()
    Dim i As Integer
    First = Sheets("MyStoreInfo").Range("B10")
    Last = Sheets("MyStoreInfo").Range("C10")
    Application.ScreenUpdating = False
    Sheets("Schedule Tool").Visible = True
    Sheets("Schedule Tool").Select
    If Sheets("MyStoreInfo").CheckBox1.Value = True Then
    For i = 4 To 1200
        If Sheets("Schedule Tool").Range("A" & i).Value = First & " " & Last And _
        Sheets("Schedule Tool").Range("A" & i).HasFormula Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        End If
    Next i
    Else
    For i = 4 To 1200
        If Sheets("Schedule Tool").Range("A" & i).Value = First & " " & Last And _
        Sheets("Schedule Tool").Range("A" & i).HasFormula Then
            Rows(i & ":" & i).EntireRow.Hidden = False
        End If
    Next i
    End If
    Sheets("Schedule Tool").Visible = False
    Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

A couple of questions:

• Why are you hiding rows on a hidden sheet?
• How do you know it's not working if the sheet is hidden?
• You could use AutoFilter to Hide\Unhide rows. Would that work for you?
• What's the formula in column A?
• What sheet is the CheckBox on? Can't tell from the code.
• What version of Excel do you have?

 

[sdoppke]

A couple of questions:

• Why are you hiding rows on a hidden sheet?
• How do you know it's not working if the sheet is hidden?
• You could use AutoFilter to Hide\Unhide rows. Would that work for you?
• What's the formula in column A?
• What sheet is the CheckBox on? Can't tell from the code.
• What version of Excel do you have?

Thanks for the reply AlphFrog

• The sheet is not hidden the code makes the sheet visible
• (not hidden)
• Im going to look at the AutoFilter option (never thought of that )
• =MyStoreInfo!$B23&" "&MyStoreInfo!$C23
• Sheet("MyStoreInfo")
• 2007

Hope that helps

sd

 

[AlphaFrog]

Perhaps you'll get ideas from this...

Private Sub CheckBox1_Click()
    
    Dim FirstNLast As String
    
    FirstNLast = Range("B10") & " " & Range("C10")
    
    With Sheets("Schedule Tool")
        If Sheets("MyStoreInfo").CheckBox1.Value = True Then
            .Range("A1:A1200").AutoFilter
            .Range("A1:A1200").AutoFilter Field:=1, Criteria1:=FirstNLast
            .Visible = True
            .Select
        Else
            .AutoFilterMode = False
            .Visible = False
        End If
    End With

End Sub