Adding Toggle to Hidden Rows Sheet

Hi guys,

I am new to the forum and Excel I am designing a spreadsheet for my fantasy football draft. I have written the following code to hide cells as I assign a Team to each player via a drop down box.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range
    Set rng = Intersect(Target, Columns(2), Rows("6:" & Rows.Count))
    If Not rng Is Nothing Then
        For Each r In rng
            With r.MergeArea
                .EntireRow.Hidden = _
                (.Cells(1).Value = "Team 1") + (.Cells(1).Value = "Team 2") + (.Cells(1).Value = "Team 3") + (.Cells(1).Value = "Team 4") + (.Cells(1).Value = "Team 5") + (.Cells(1).Value = "Team 6") + (.Cells(1).Value = "Team 7") + (.Cells(1).Value = "Team 8") + (.Cells(1).Value = "Team 9") + (.Cells(1).Value = "Team 10")
            End With
        Next
    End If
    Set rng = Nothing
End Sub

I know this code can probably be done much simpler but this is the way I could get it to work .

My problem comes in trying to add a Toggle button to switch my hidden rows on and off. I can't seem to figure out what to do with the duplicate Private Sub that the toggle button code uses.

I also am having a hard time writing the code to have each player's name show up on the Team's tab after being selected on the Draft Tab. I would like each player to show up in the columns associated with each team by position if possible. For example if Adrian Peterson is drafted by Team 1 he would show up in the RB slot for team 1. If the RB slots are full then that player would show up on the BE.

I don't know if this is possible but I was hoping someone can help point me in the right direction.

Thanks,


TKBulldog

Here's a project I worked on with another OP here in the forum just a couple of weeks ago, seems to be the same idea. Players chosen show up in the specified teams slotted Roster. Overflow players appear on the Bench.

1) Select a Position in column C
2) Select a person still available from that position in column D
3) The lists will shorten themselves as people are drafted, no VBA is used in the project

Everything's possible, of course. But I'm all "footballed out" this season already, so I'll send this to the cavalry.

Hi TKbulldog,

See if the attached file with two command buttons doesn't do what you want.
The code looks like:

Option Explicit

Sub UnhideAll()

    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("B5").Select
End Sub

Sub HideSelected()

Dim LastPickRow As Double
Dim RowCtr As Double

LastPickRow = Cells(248, "B").End(xlUp).Row
    For RowCtr = 6 To LastPickRow
        If Len(Cells(RowCtr, "B")) > 0 Then
            Cells(RowCtr, "B").EntireRow.Hidden = True
        End If
    Next RowCtr
    Range("B5").Select
End Sub

Here's another, though I modified sheet 2 for my convenience:

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.