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 on January 12, 2018 in VBA: General.
Add Comment
2 Answer(s)

Here is what I came up with.  The macro will color the text in the cell white or black depending on the status of the  check box.

Each check box is assigned to the same macro. Each check box has a cell link to the location is it in. Example check box 1 has a cell link in cell “A1” and so on.

RE: Excel-Hiding rows with specific text and multiple textboxes

Here is the code. Hope this works for you.

Sub hid()

Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer
Dim r As Integer
Dim box As Integer

BeginRow = 10
EndRow = 205
ChkCol = 2
r = 1

For box = 1 To 5 ‘ this represents the number of check boxes being used
For RowCnt = BeginRow To EndRow

If Cells(r, 1) = True Then
If Cells(RowCnt, ChkCol).Value = Cells(r, 2) Then
Cells(RowCnt, ChkCol).Font.Color = RGB(255, 255, 255)
End If
End If

If Cells(r, 1) <> True Then
If Cells(RowCnt, ChkCol).Value = Cells(r, 2) Then
Cells(RowCnt, ChkCol).Font.Color = RGB(0, 0, 0)
End If

End If
Next RowCnt

r = r + 1
Next box
End Sub

Expert Answered on January 13, 2018.

Thank you Hyside,

Thats a option I had not thought of, I will try it when I get some spare time.

I’m wondering though, since it just changes text colour, and doesn’t hide the row/s

if I want to only show, say Frigates and Corvettes, then I sort them with say highest to lowest shields, won’t this solution sort everything still? It would just show me a whole heap of ‘blank’ rows in between the items I am looking for, and the user would be forced to scroll perhaps all the way down 200 rows?
I was hoping to actually hide the rows so when sorted by a stat, I only see the say 10-15 ships that are left, and all clustered together, making it easy to compare.

on January 16, 2018.
Add Comment

Hi, Ok let’s try hiding the rows then like you did in the beginning.

RE: Excel-Hiding rows with specific text and multiple textboxes

[Code]

Sub hidrow()

Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer
Dim r As Integer
Dim box As Integer

BeginRow = 10
EndRow = 205
ChkCol = 2
r = 1

Application.ScreenUpdating = False

For box = 1 To 5 ‘ this represents the number of check boxes being used
For RowCnt = BeginRow To EndRow

If Cells(r, 1) = True Then
If Cells(BeginRow, ChkCol).Value = Cells(r, 2) Then
Cells(BeginRow, ChkCol).Rows.EntireRow.Hidden = True
End If
End If
If Cells(r, 1) = False Then
If Cells(BeginRow, ChkCol).Value = Cells(r, 2) Then
Cells(BeginRow, ChkCol).Rows.EntireRow.Hidden = False
End If
End If

BeginRow = BeginRow + 1

Next RowCnt

r = r + 1
BeginRow = 10
EndRow = 30
Next box

End Sub

Expert Answered on January 25, 2018.

Hey Hyside2,

I tried your code, and it wouldn’t work for me, but I’m sure its me missing something.. anyway, I have been playing with it and think I got it working a different way

Private Sub CheckBox1_Click()
BeginRow = 12
EndRow = 207
ChkCol = 2
Application.ScreenUpdating = False
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Super Capital" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = Not CheckBox1
End If
Next RowCnt
Application.ScreenUpdating = True
End Sub

I think with the ‘else’ part in there, it was causing issues.. once I took that out all check boxes ticked stayed hidden, and unchecking them revealed them.  The screen updating part made it all a lot quicker as well.

Thanks so much for the assistance, without reading your examples I probably would not have worked it out!

on February 6, 2018.

I am glad I was able to contribute to your success!

on February 7, 2018.
Add Comment
  • 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.