VLOOKUP in Checkbox Macro

[Chestertim]

I am trying to use a checkbox to hide a row of data. I was able to set it up to hide a specific row but I am trying to hide a row based on the value of a cell in B5:B17 (the data gets sorted and moves to different rows depending on various other macro sorts. I have looked everywhere on here to find a vlookup or find function for a macro code that I can put into the checkbox1 sub. Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

It might be helpful if you could show us an example of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, could you please post the VBA code you have so far?
You can see here how to use Code Tags to make your code look nice and easily readable: How to Post Your VBA Code

 

[Chestertim]

here is a sample of my sheet. I am trying to create a checkbox that will allow me to hide a row in the document depending on who is attending that day. For example, The names on line 10 and 14 will be taking turns on different days and I would like to be able to hide the person that is not there. The list gets sorted and therefore the cell that those names are in chages depending on the information in the document

 

[Chestertim]

I was able to create a checkbox with a macro that will hide a specific line but can't figure out how to make this happen when the system needs to find the cell with the specific data in it

 

[Chestertim]

This runs without an error but nothing happens. I'm lost and a novice at this as you can tell.

Sub CheckBox5_Click_Caymon()
Dim r As Long

If Checkbox5 = False Then

Application.ScreenUpdating = False


' Check rows 5:17
For r = 5 To 17
Rows(r).EntireRow.Hidden = (Cells(r, "B") = "Caymon Spires")



Application.ScreenUpdating = True
Next r
Else
If Checkbox5 = True Then
Application.ScreenUpdating = False

' Check rows 5:17
For r = 5 To 17
Rows(r).EntireRow.ShowAllData = (Cells(r, "B") = "Caymon Spires")
Next r
Application.ScreenUpdating = True


End If
End If




End Sub

 

[Joe4]

On your example, can you explain which rows should be hidden and why?

 

[Chestertim]

This is a Golf Trip handicap sheet I use with many different pages and macros. This particular page tabulates and shows each golfers handicap after each round. This year, instead of 12 golfers, we have two people that will be taking turns on different days for the 12 player. Based on which golfer is not there that day, I want to hide him from the sheet. The placement on the sheet is can move based on their handicap. The two golfers for this year are Cayman and Jacob. As I noted earlier, I was able to create a checkbox for each golfer that would hide a specific line, but once the page gets sorted and I click the button it only hides that specific row not the one that the player is in. For Example: when I used:

Private Sub CheckBox2_Click()
If CheckBox2 = False Then
Rows(10).EntireRow.Hidden = True

Else
Rows(10).EntireRow.Hidden = False

End If

End Sub

This allowed me to check the box and hide row 10 and it would reappear when I unchecked the box. Unfortunately, the person I am trying to hide may not be on row 10 at the time I am checking the box. I would like to be able to do some type of Vlookup or find and then EntireRow.Hidden based on it finding that person's name.

Thanks for the help. I hope this sheds more light on the effort

 

[Chestertim]

On your example, can you explain which rows should be hidden and why?

I now have it working but not sure if this is the most efficient. I ended up doing it this way:

thanks for the effort. Any additional info you think I need is welcome..

_________________________________________________________________________________________________________________________

Private Sub CheckBox1_Click()

Dim r As Long

If CheckBox1 = False Then
Application.ScreenUpdating = False

' Check rows 5:17
For r = 5 To 17
Rows(r).EntireRow.Hidden = (Cells(r, "B") = "Jacob Spires")
Next r

Application.ScreenUpdating = True

Else
Rows("5:17").Select
Selection.EntireRow.Hidden = False
Range("B2:V2").Select

End If

End Sub


Private Sub CheckBox2_Click()
If CheckBox2 = False Then
Application.ScreenUpdating = False

' Check rows 5:17
For r = 5 To 17
Rows(r).EntireRow.Hidden = (Cells(r, "B") = "Cayman Spires")
Next r

Application.ScreenUpdating = True

Else
Rows("5:17").Select
Selection.EntireRow.Hidden = False
Range("B2:V2").Select


End If

End Sub