I'm part of a rec league softball team that plays in a league with an end-of-season tournament. Before the tournament, the league organizers send out the tournament bracket, in Word format. I personally feel that the Word document is ugly, and the way the organizers set it up, it's not always easy to read. (This year's version of the bracket spans 4 pages!) I want to try and format the bracket in Excel, and have a series of macros to "advance" the winners by double clicking on the team name.
The problem is that it's a double elimination tournament. So, really, there are 2 brackets, the Winners Bracket, and the Loser's bracket. To start with, every team is in the Winner's bracket. If they win, they stay in that bracket. But, if they lose, they get sent to the Loser's bracket. (Once you're in the Loser's bracket, if you lose, you're done)
I can make a decent looking bracket reasonably easily, and I even have some code that will advance the winners along the lines of the bracket. The trouble I'm running into is finding an easy way to move the team names of the losers into the Loser's bracket. I know that I can setup code for each game that is basically an if-then statement ... If I click on Team1, then they advance and Team2's name goes to the appropriate cell on the loser's bracket ... but that's a very manual solution, and not terribly re-usable from one year to the next (the # of teams/games is not always consistent)
Here is the code that I'm using that will advance the team along the bracket lines. This code is working, and works well for a single-elimination tournament.
I've also attached the spreadsheet that I've created for this year's tournament. Each game is numbered, G1, G2, etc ... so, "Loser G3" would be the loser of Game #3. The winners bracket is represented with green highlights, red for the losers bracket.
Any advice would be greatly appreciated.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim row As Integer
Dim col As Integer
Dim Direction As Integer
Dim reachedTop As Boolean
Dim reachedBottom As Boolean
Dim Border
Dim GoodCell
Set GoodCell = Nothing
row = Target.row
col = Target.Column
With ActiveSheet:
If col <= 7 Then
Border = xlEdgeLeft
col = col + 1
ElseIf col >= 7 Then
Border = xlEdgeRight
col = col - 1
Else
Exit Sub
End If
If .Cells(row, col).Borders(Border).LineStyle = xlNone Then
reachedTop = True
Direction = 1
row = row + Direction
Else
Direction = -1
End If
Do
If .Cells(row, col).Borders(Border).LineStyle = xlNone Then
If Direction > 0 Then
reachedBottom = True
Direction = -1
Else
reachedTop = True
Direction = 1
End If
End If
If .Cells(row, col).Borders(xlEdgeBottom).LineStyle <> xlNone Then
Set GoodCell = .Cells(row, col)
Exit Do
End If
If reachedTop And reachedBottom Then Exit Do
row = row + Direction
Loop While True
End With
If GoodCell Is Nothing Then
MsgBox "Invalid Entry"
Else
GoodCell.Value = Target.Value
End If
End Sub
Bookmarks