Excel-Hiding rows with specific text and multiple textboxes

Hi all, I’m trying to build a database using excel.  Not that it should matter, but its to  search stats for ships in a game.

I’ve never used VBA before, so everything I’ve done has been with lots of googling.

Basically I have 8 ship types with multiple ships in each type.  I want a tick box to show as many or as few ship types as is selected.  I cant just hide row numbers, as I want it to be sorted Max-Min on different stats, so I’m hiding based on the name in Column B, which is s the ship type (Capital, Frigate, Corvette, Gunboat, Fighter)  What I have works to an extent, if I untick gunboat box, that type is hidden. The problem is if I untick another one (so, want to hide 2 types) the first one is shown again, even though the box is still un ticked.  Below is the code for 2 types, and its just repeated for the rest, with name changes.   How can I change it so if I untick more than 1 box, ALL those are hidden?

Row 10 is where the data starts, row 205 is the end of the list and the ship type to check is in Col.2 (B)

 

Private Sub CheckBox5_Click()
BeginRow = 10
EndRow = 205
ChkCol = 2
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Gunboat" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = Not CheckBox5
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Private Sub CheckBox6_Click()
BeginRow = 10
EndRow = 205
ChkCol = 2
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Fighter" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = Not CheckBox6
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Participant Asked 13 hours ago in VBA: General.
Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.