Glasgowsmile
Active Member
- Joined
- Apr 14, 2018
- Messages
- 280
- Office Version
- 365
- Platform
- Windows
I want to create a checkbox that will hide and unhide a specific row based on the value of another cell.
Sheet10 Data (Column K):
A
A1
A2
A3
Sheet9 Data (Column B):
A1
A2
A
A3
In this example I click a checkbox next to A on Sheet10 and it looks at the 'A' value then goes to find it in Sheet9 because the location in Column B isn't always going to be the same. If it finds it and the column isn't hidden then it hides it. This part of the code works... what doesn't work is the opposite, when you uncheck the box it doesn't unhide the value.
Instead I get a run-time error '91': Object variable or With block variable not set
Sheet10 Data (Column K):
A
A1
A2
A3
Sheet9 Data (Column B):
A1
A2
A
A3
In this example I click a checkbox next to A on Sheet10 and it looks at the 'A' value then goes to find it in Sheet9 because the location in Column B isn't always going to be the same. If it finds it and the column isn't hidden then it hides it. This part of the code works... what doesn't work is the opposite, when you uncheck the box it doesn't unhide the value.
Instead I get a run-time error '91': Object variable or With block variable not set
VBA Code:
Sub CheckBox3_Click()
Dim FindString As String
Dim Rng As Range
FindString = Sheet10.Range("K2").Value
With Sheet9.Range("B:B") 'searches all of column B
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing And Rng.EntireRow.Hidden = False Then
Rng.EntireRow.Hidden = True
ElseIf Rng.EntireRow.Hidden = True Then
Rng.EntireRow.Hidden = False
End If
End With
End Sub