Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NCAA Basketball Bracket VBA Color Coding for Winners on Worksheet_ChangeEvent...
Hello,
I am creating an NCAA Basketball Bracket pool to determine and show who are the winners of the tournament in the Final Four, the Finals, and lastly, the National Champion. What I need is some help in getting the VBA Worksheet_Change Event to work properly when the Select Winners Per Round macro (found under the Macros option on the menu bar) is selected and used to populate the winners of each game. For each winner in the Final Four, the Finals, and lastly, the National Champion rounds, the names should be highlighted in the ranges, O56:R56, O57:P57, and O58 and then respectively in the matching names in the range, X23:X37. For those interested in downloading and seeing the code/situation I am describing I have uploaded the spreadsheet to my website found he http://members.dslextreme.com/users/...id/bracket.xls I have tried a combination of this code, but cannot get it to work correctly: Set cellName = Range("X23:X37") Set cellTest = Range("O56") colorName = Cells(Application.WorksheetFunction.Match(cellTest , cellName, 0) _ + 22, 24).Address(False, False) ' Range("O56").Interior.ColorIndex = 4 Range(colorName).Interior.ColorIndex = 4 It ends up with errors either within the Worksheet_Change event or in the testme() sub. Additionally, another option under Macros on the menu bar, Select Winners Per Round macro, is the ability to go back to a previous round to make corrections to the radio/option buttons selected. If this is the case, then on a name change, the wrong name's ".Interior.ColorIndex" should be changed to xlNone. This was also causing an error. Feel free to test out my MsgBox code,* see below, in 'Sub testme()' to see how the cells don't update until the form is closed. I am probably pretty close, but I am just missing something to get it to function correctly. ' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " & Range("X23") ' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then ' MsgBox "boo!" ' Range("X23").Interior.ColorIndex = 4 ' Else ' MsgBox "who!" ' Range("X23").Interior.ColorIndex = xlNone ' End If Please let me know if anyone has any questions as needed. I am hoping to have this finished by Sunday, March 13. Thanks much, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NCAA Basketball Bracket VBA Color Coding for Winners on Worksheet_Change Event...
Paul,
I had a quick look at your WS and whilst I can't immediately give you an answer to your questions, have you considered using a set of classes and collections to keep track of your people, teams, round etc.That way, the class can keep track of it's status (round, won/lost, position, colour index etc) and when an update is required you just redraw the whole table. Just an idea. NickHK "Paul B." wrote in message ... Hello, I am creating an NCAA Basketball Bracket pool to determine and show who are the winners of the tournament in the Final Four, the Finals, and lastly, the National Champion. What I need is some help in getting the VBA Worksheet_Change Event to work properly when the Select Winners Per Round macro (found under the Macros option on the menu bar) is selected and used to populate the winners of each game. For each winner in the Final Four, the Finals, and lastly, the National Champion rounds, the names should be highlighted in the ranges, O56:R56, O57:P57, and O58 and then respectively in the matching names in the range, X23:X37. For those interested in downloading and seeing the code/situation I am describing I have uploaded the spreadsheet to my website found he http://members.dslextreme.com/users/...id/bracket.xls I have tried a combination of this code, but cannot get it to work correctly: Set cellName = Range("X23:X37") Set cellTest = Range("O56") colorName = Cells(Application.WorksheetFunction.Match(cellTest , cellName, 0) _ + 22, 24).Address(False, False) ' Range("O56").Interior.ColorIndex = 4 Range(colorName).Interior.ColorIndex = 4 It ends up with errors either within the Worksheet_Change event or in the testme() sub. Additionally, another option under Macros on the menu bar, Select Winners Per Round macro, is the ability to go back to a previous round to make corrections to the radio/option buttons selected. If this is the case, then on a name change, the wrong name's ".Interior.ColorIndex" should be changed to xlNone. This was also causing an error. Feel free to test out my MsgBox code,* see below, in 'Sub testme()' to see how the cells don't update until the form is closed. I am probably pretty close, but I am just missing something to get it to function correctly. ' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " & Range("X23") ' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then ' MsgBox "boo!" ' Range("X23").Interior.ColorIndex = 4 ' Else ' MsgBox "who!" ' Range("X23").Interior.ColorIndex = xlNone ' End If Please let me know if anyone has any questions as needed. I am hoping to have this finished by Sunday, March 13. Thanks much, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NCAA Basketball Bracket VBA Color Coding for Winners onWorksheet_Change Event...
Nick,
I hadn't thought of doing it the way you suggest, mostly because I am not sure how to approach it this way. Would you please show me how it should be written? Thanks much, Paul NickHK wrote: Paul, I had a quick look at your WS and whilst I can't immediately give you an answer to your questions, have you considered using a set of classes and collections to keep track of your people, teams, round etc.That way, the class can keep track of it's status (round, won/lost, position, colour index etc) and when an update is required you just redraw the whole table. Just an idea. NickHK "Paul B." wrote in message ... Hello, I am creating an NCAA Basketball Bracket pool to determine and show who are the winners of the tournament in the Final Four, the Finals, and lastly, the National Champion. What I need is some help in getting the VBA Worksheet_Change Event to work properly when the Select Winners Per Round macro (found under the Macros option on the menu bar) is selected and used to populate the winners of each game. For each winner in the Final Four, the Finals, and lastly, the National Champion rounds, the names should be highlighted in the ranges, O56:R56, O57:P57, and O58 and then respectively in the matching names in the range, X23:X37. For those interested in downloading and seeing the code/situation I am describing I have uploaded the spreadsheet to my website found he http://members.dslextreme.com/users/...id/bracket.xls I have tried a combination of this code, but cannot get it to work correctly: Set cellName = Range("X23:X37") Set cellTest = Range("O56") colorName = Cells(Application.WorksheetFunction.Match(cellTest , cellName, 0) _ + 22, 24).Address(False, False) ' Range("O56").Interior.ColorIndex = 4 Range(colorName).Interior.ColorIndex = 4 It ends up with errors either within the Worksheet_Change event or in the testme() sub. Additionally, another option under Macros on the menu bar, Select Winners Per Round macro, is the ability to go back to a previous round to make corrections to the radio/option buttons selected. If this is the case, then on a name change, the wrong name's ".Interior.ColorIndex" should be changed to xlNone. This was also causing an error. Feel free to test out my MsgBox code,* see below, in 'Sub testme()' to see how the cells don't update until the form is closed. I am probably pretty close, but I am just missing something to get it to function correctly. ' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " & Range("X23") ' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then ' MsgBox "boo!" ' Range("X23").Interior.ColorIndex = 4 ' Else ' MsgBox "who!" ' Range("X23").Interior.ColorIndex = xlNone ' End If Please let me know if anyone has any questions as needed. I am hoping to have this finished by Sunday, March 13. Thanks much, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NCAA Basketball Bracket VBA Color Coding for Winners on Worksheet_Change Event...
Paul,
Something like: Main class (League) - Property: Year - Property: Teams (Collection of Team classes. Array of teams names; read/write if these names can change from year to year) - Method: OutputStartUp (draw this initial line up, in Excel or to picture box etc) - Method: OutputCurrent (draw this current situation, in Excel or to picture box etc) Also you would need a collections of: PersonPlaying class - Property: BackColour Team class - Property: Name - Property: Holder (A reference to the PersonPlaying class that "owns" this team) - Property: CurrentRound etc. Not sure it would make sense for you go this route, but then you would not necessarily be tied to Excel; you just add another option to the OutputToFormat, as all data is known within the classes. You would of course have to store the data of the current situation somehow/where between running of your app. NickHK "Paul B." wrote in message ... Nick, I hadn't thought of doing it the way you suggest, mostly because I am not sure how to approach it this way. Would you please show me how it should be written? Thanks much, Paul NickHK wrote: Paul, I had a quick look at your WS and whilst I can't immediately give you an answer to your questions, have you considered using a set of classes and collections to keep track of your people, teams, round etc.That way, the class can keep track of it's status (round, won/lost, position, colour index etc) and when an update is required you just redraw the whole table. Just an idea. NickHK "Paul B." wrote in message ... Hello, I am creating an NCAA Basketball Bracket pool to determine and show who are the winners of the tournament in the Final Four, the Finals, and lastly, the National Champion. What I need is some help in getting the VBA Worksheet_Change Event to work properly when the Select Winners Per Round macro (found under the Macros option on the menu bar) is selected and used to populate the winners of each game. For each winner in the Final Four, the Finals, and lastly, the National Champion rounds, the names should be highlighted in the ranges, O56:R56, O57:P57, and O58 and then respectively in the matching names in the range, X23:X37. For those interested in downloading and seeing the code/situation I am describing I have uploaded the spreadsheet to my website found he http://members.dslextreme.com/users/...id/bracket.xls I have tried a combination of this code, but cannot get it to work correctly: Set cellName = Range("X23:X37") Set cellTest = Range("O56") colorName = Cells(Application.WorksheetFunction.Match(cellTest , cellName, 0) _ + 22, 24).Address(False, False) ' Range("O56").Interior.ColorIndex = 4 Range(colorName).Interior.ColorIndex = 4 It ends up with errors either within the Worksheet_Change event or in the testme() sub. Additionally, another option under Macros on the menu bar, Select Winners Per Round macro, is the ability to go back to a previous round to make corrections to the radio/option buttons selected. If this is the case, then on a name change, the wrong name's ".Interior.ColorIndex" should be changed to xlNone. This was also causing an error. Feel free to test out my MsgBox code,* see below, in 'Sub testme()' to see how the cells don't update until the form is closed. I am probably pretty close, but I am just missing something to get it to function correctly. ' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " & Range("X23") ' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then ' MsgBox "boo!" ' Range("X23").Interior.ColorIndex = 4 ' Else ' MsgBox "who!" ' Range("X23").Interior.ColorIndex = xlNone ' End If Please let me know if anyone has any questions as needed. I am hoping to have this finished by Sunday, March 13. Thanks much, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
NCAA Basketball Bracket VBA Color Coding for Winners on Worksheet_Change Event...
Paul B.,
Found this which may be of interest to you: http://www.dicks-blog.com/excel/2005%20Bracket.zip NickHK "Paul B." wrote in message ... Hello, I am creating an NCAA Basketball Bracket pool to determine and show who are the winners of the tournament in the Final Four, the Finals, and lastly, the National Champion. What I need is some help in getting the VBA Worksheet_Change Event to work properly when the Select Winners Per Round macro (found under the Macros option on the menu bar) is selected and used to populate the winners of each game. For each winner in the Final Four, the Finals, and lastly, the National Champion rounds, the names should be highlighted in the ranges, O56:R56, O57:P57, and O58 and then respectively in the matching names in the range, X23:X37. For those interested in downloading and seeing the code/situation I am describing I have uploaded the spreadsheet to my website found he http://members.dslextreme.com/users/...id/bracket.xls I have tried a combination of this code, but cannot get it to work correctly: Set cellName = Range("X23:X37") Set cellTest = Range("O56") colorName = Cells(Application.WorksheetFunction.Match(cellTest , cellName, 0) _ + 22, 24).Address(False, False) ' Range("O56").Interior.ColorIndex = 4 Range(colorName).Interior.ColorIndex = 4 It ends up with errors either within the Worksheet_Change event or in the testme() sub. Additionally, another option under Macros on the menu bar, Select Winners Per Round macro, is the ability to go back to a previous round to make corrections to the radio/option buttons selected. If this is the case, then on a name change, the wrong name's ".Interior.ColorIndex" should be changed to xlNone. This was also causing an error. Feel free to test out my MsgBox code,* see below, in 'Sub testme()' to see how the cells don't update until the form is closed. I am probably pretty close, but I am just missing something to get it to function correctly. ' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " & Range("X23") ' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then ' MsgBox "boo!" ' Range("X23").Interior.ColorIndex = 4 ' Else ' MsgBox "who!" ' Range("X23").Interior.ColorIndex = xlNone ' End If Please let me know if anyone has any questions as needed. I am hoping to have this finished by Sunday, March 13. Thanks much, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
basketball bracket | Excel Worksheet Functions | |||
ncaa basketball bracket - repost | Excel Discussion (Misc queries) | |||
NCAA Basketball Bracket 2008 | Excel Discussion (Misc queries) | |||
NCAA 2007 NCAA Bracket and Tracker | Excel Discussion (Misc queries) | |||
I have been looking for Pivot table for NCAA Basketball 2004-2005. | Excel Worksheet Functions |