Removed all of the Active-X buttons and replaced them with a
customized graphic. (I got tired of people writing to me, telling me
that their Active-X Buttons were messed up. It was always a graphics
card conflict / issue and not a bug in my program.)
Recoded the program to allow for four additional weeks, to
accommodate the playoffs. (The Running Totals worksheet tab
was updated, as was the NFL_Schedule tab, and the Weekly Pick
Replaced all references of San Diego Chargers to Los Angeles
Chargers. And now with two teams in Los Angeles, LAR is now the
abbreviation for the Los Angeles Rams, (and not just LA) and LAC is
the abbreviation for the Los Angeles Chargers.
Entered the 2017 NFL Schedule.
Removed a few of the speed bottlenecks, so the program runs a bit
The macro in the hidden "data" worksheet now also updates the
NFL_Schedule tab, when it downloads the NFL schedule for the
Very large pools (100 or more players) still take several seconds to
load, as the programming logic goes through the sub routines to to
determine the winners, to highlight the necessary cells, etc. Thus,
I thought it best to add an optional "progress bar" in the Status
Bar area (bottom of the screen), so the user, if using a slow
computer, knows that "something is happening" - that the program is
still working and hasn't crashed.
Allowed the user to set the number of maximum players themselves.
(It was initially hard coded to 200 and then later hard coded to
500.) In theory, with a smaller number, the program should run a bit
faster. If you only have a dozen or two members in your pool, just
set the max number of players to 30 or so.
Removed the code that generated the Checksum figure. (This was an
old feature I needed a long time ago, that I'm sure is not used by
Added the Testimonials worksheet tab. (Feel free to hide it
so it is out of the way.)
Added an option for the user to download the game scores from either
the NFL website or the CBS sports website.
Added a CLEAR DATA button in the Running_Totals worksheet
Cleaned up the weekly pick sheet forms, so the formula doesn't
display that "0" during the weeks with byes.
Dozens of minor cosmetic changes, too numerous to mention, were made
to the program.
The program was attempting to add the
spread to the score of each NFL game. When the picks are first
loaded, there IS no score. The games haven't been played yet
and the score is displayed as a dash/hyphen. You can't add a
numeric field (pointspread) to an alphanumeric field (a dash).
An error results. Fixed.
Changed the buttons in the
Running_Totals worksheet tab from an Active-X button to a
Form Control button.
Under rare conditions, players who were still mathematically alive
were being displayed as if they were not. Fixed.
Entered the 2016 NFL schedule.
Replaced all references to St. Louis Rams with Los Angeles
Replaced Jac with Jax in the code, where necessary. (The NFL
now seems to be using JAX as the three-letter abbreviation for
Added my Weekly Pick Sheet Form to this workbook.
The changes made to 2.28 caused a potential bug when loading
The program was not stripping leading or trailing spaces from the
player's Monday Night point total prediction. There shouldn't be
any trailing or leading spaces, of course… it should just be a
number, and the program IS computing the difference correctly,
from the actual Monday Night total. Still, if a prediction is
entered with lots of such spaces, the number isn't centered in the
cell and it looks funny. Fixed.
Apparently the player name and player picks can contain ascii
character 160, in addition to leading or trailing spaces. (Via picks
sent to the host via a cell phone??) I was only checking for and
removing the spaces, not this weird "invisible" ascii
character. And, if ascii character 160 was present, the program
didn't recognize the team name. Fixed.
Increased the limit to the number of pool players form 200 to 500.
Fixed a glitch. Under certain circumstances, the program wasn't
properly displaying who was still alive and who was eliminated,
despite this option being set to "Yes" in the User_Options tab.
(It just required adding a dash ("-") in the NFL_Schedule tab for
the score, for all unplayed games.)
If a user entered a Monday Night score prediction as a non-integer,
the program was rounding it up or down accordingly, to the nearest
integer. This meant a total score prediction of 55.1, for example,
would get rounded down to 55. If another player entered 55 as
their prediction, the program considered these two players to
have same Monday Night tie-breaker score, and graded
accordingly. This isn't the case, of course. A prediction of 55 is
closer to a final score of 47, for example, than 55.1 is to 47.
Although I see NO advantage in guessing scores as non-integers (only
disadvantages - think about it), if a player wishes guess this way,
the program should grade accordingly.
Are you kidding me? Updates somewhere created a bug, with
bye weeks, when no bug existed before. The program expected another
game was still to be played, and didn't calculate the final winner.
This is fixed... I think.
The Player Nickname sorting link, the
Total Wins sorting link, and the Win % sorting link, all located in
the Running Totals worksheet tab, didn't seem to be working for
everyone. I removed the links and added a toggle sort button
Corrected a mistake in the Schedule, in the NFL_Schedule tab.
(Washington hosts Philly in Week 4, not Tampa Bay.)
Added a message in the Running_Totals tab, to let the user know this
tab is populated by hitting the SAVE SCORES button on the Main
The Player Nickname link, in the Running
Totals worksheet tab, that sorts the list alphabetically, wasn't
working. (Seriously?) Fixed.
Tiny glitch with the colors of the toggle buttons not working,
involving St. Louis. Fixed.
Although no one has reported any bug when the Price Is Right
Scoring option is set to Yes, I think I fixed a potential
Pools with 100+ players run very slowly, after the player returns to
the Main worksheet tab after visiting the User Options tab, even if
no options were changed. Now, if no options were changed there
should no be no delay.
If the user modifies the Monday Night Total score in cell S10
(because their pool uses a different number. For example, just the
score of the winning team), the program will now SAVE the score the
user enters in cell S10, in the Running Totals tab, when the scores
If the user did enter their own Monday night point total in Cell
S10, the highlight colors depicting which players were still alive
and which players were dead was not quite correct. Fixed.
Entered the 2015 NFL Schedule.
I eliminated one of the worksheet tabs.
I combined the NFL_Schedule tab and the Saved_Results tab into one
tab. (They basically contained the same data anyway.) I also
redesigned that tab, to be more consistent with nfl.com's website,
in regards to the way the final scores are displayed.
When the scores are saved in the NFL_Schedule tab, I'm setting the
font to bold for the winning team and the winning score. (Again, consistent
with the nfl.com's website, and what you often see on television.)
When a team didn't score any points in the game, the Saved_Results
tab (now the NFL_Schedule tab) was displaying their score with a
dash, instead of a zero. Fixed.
I removed the toggle button that changes all winning teams to the
Home Team or to the Visiting Team. (That button was nothing but
When a prior week was loaded that contained a tie game, the toggle
button didn't display the word TIE. Fixed.
Rewrote the code that shades the cells of all non winners, after all
games have been completed.
If the program was opened and the Display NFL Scores checkbox was
not checked, and then immediately checked, the program crashed.
User Request. Added a new user option to allow the same player to
submit more than one set of picks and yet combine all of these
results into the same player in the Running Totals worksheet tab.
The player's picks should be appended at the end with parenthesis.
Example: Edward (2), Edward (3), etc.
Renamed the SAVE DESIGNATED WINNERS button to SAVE SCORES & RESULTS.
Changed the size of all four buttons (and the checkbox) so they are
shorter (but wider) to allow for more data vertically.
For Week 1 of 2014 (for example), the program downloads the scores
from www.nfl.com/scores/2014/REG1. Rather than hard-code the year
(2014) into the program, it's now a variable that the user can
change themselves each year, in the User_Options tab. Now the only
other thing that needs to be changed each year is the NFL schedule, and
the program will work each year, with no other modifications.
Added an option for the user to set their preferred row height for
the Main tab and the Running_Totals tab.
Added the user option to auto scroll to the top rows of most
worksheet tabs. (All but the Player_Picks tab.)
Added a hyperlink to the name of this program at the top of this
page, to the calculator's website.
When saving the final results to the Running_Totals tab, the total
wins for the player who won is now set in a bold font.
Bug found: The program didn't recognize "Buccaneers" or "Chiefs" as
valid mascot names. Fixed.
Removed the instructions on how to get rid of the "Data Connections
Are Disabled" message, since I think I've solved that for good.
If some of the teams in the NFL_Schedule tab were listed in
uppercase, they didn't match the player's selections if entered in
Dozens of minor cosmetic changes made to the program, too numerous
Replaced the TOGGLE SORT button in the
new Running_Totals worksheet tab with Header Links instead. Single
clicking the Header Link will sort that column!
"SL" is now a valid entry for the Cardinials, "NOS" is now valid for
the Saints, "NEP" is now valid for the Patriots and "sd" and "Sd"
are now valid for the Chargers. (Thanks Joe.)
If an office pool's rules didn't require the user to select all of
the games, the games and buttons weren't always loading correctly.
Fixed. (Thanks Tony.)
to determine who was still alive got screwed up. Fixed.
The program now recognizes even more
teams abbreviations, as well as a few common misspellings. For
example, it knows that "Cheifs" = Kansas City Chiefs, "Id" =
Indianapolis Colts, "Boys" = Dallas Cowboys, "Gaints" = New York
The program now can handle player picks that are entered in
different columns than one another! To clarify, when entering picks in the Player_Picks tab, the user doesn't have to keep the same teams
together in each column! All that matters is that each player
submit a home or a visiting team for each game. The user can
enter them in or out of order if he/she wishes. The program
will sort everything accordingly! (However, for clarity, the
user should enter each game in the same column.)
I improved the screen display for office pools that don't use a
total score tie-breaker for the Monday Night game.
I removed the requirement for the user to designate if their pool
uses Confidence Point scoring or not. The program can again
determine this on its own.
I added the Running_Totals worksheet tab, and all the logic behind
it, at the request of several users. To populate it, you click
the Save Designated Winners button.
The program now downloads past weeks scores, if requested, from the
nfl.com website, rather than the repole.com website I was using.
(Reason: The repole.com website is down and it doesn't appear it
will be up again any time soon. So let's retrieve past scores from
another website, and I might as well use nfl.com.)
After the final game had been played, the program didn't highlight
the player who won the pool. Instead, the program was only
highlighting the top x number of players who all were tied at the
top with the same number of wins. It didn't go one step
further and determine the overall winner, by taking the tie-breaker
score into effect. It does now.
I removed one of the buttons. (Slightly less clutter on the screen
this way.) The SET all VISITING TEAMS as WINNERS button and the SET
all HOME TEAMS as WINNERS buttons have been combined. This button is
now a toggle button.
Added a Status Bar message after most actions.
If the user entered their own scores in the Saved_Results tab (which
a user wanted to do, because they were no scores available yet to
download from the repole.com website, it turns out the user had
to enter them in the same order as in the Player_Picks tab!
Darn! Not ideal. If they entered them in the same order
of the NFL_Schedule tab, the program didn't work. This is
fixed. The user can now enter the winners and final scores in the
Saved_Results tab in any order. (But the user should still let the
program enter these scores, if possible.)
Gosh darn it. I found two mistakes
in the NFL schedule. (Not my fault, other than I didn't make
time to double-check it. The source I got the schedule
from was wrong!)
The calculator will now, optionally,
take into account the NFL pointspread when determining the winning
team. (User request - their pool uses the pointspread.) The user
must enter the pointspread themselves each week, in the NFL_Schedule
Sorting the list of players automatically, by the number of wins, is
now a user option.
If the user entered a player's name in
the Player_Picks worksheet tab, but hadn't yet filled in that
player's picks (because they didn't have them yet, but were
expecting them) the program
crashed. Fixed. (Thanks Keith, for letting me know about this.)
If you're using Confidence Points, the program will now check to
make sure that everyone's combined Confidence Points equal the same
total. If not, the user will be alerted with an error message.
I cleaned up and changed the format of the Main tab! I think it
looks a lot better! As you can see, the user buttons are now at the
top of the screen, rather than at the side. Also, each player's Monday Night tie-breaker score is now displayed,
rather than keeping it hidden. Also, the all-important difference between the
player's Monday Night prediction and the actual total is displayed.
I spent more time testing the program when Confidence Points are
used, and I may have fixed a few potential bugs (all of them just
cosmetic in nature).
When the picks are retrieved via the Main Page, the Print Area is
now optionally set, and is based upon the number of players who are
playing that week, to make the page more attractive when printing.
The very first time the workbook is opened (and not saved) a brief
message appears informing the user that picks must be entered in the
Player_Picks worksheet tab. (This is the #1 question that I get
e-mailed about. Despite all of my instructions to the contrary, new
users begin entering on the Main worksheet tab instead of on the
Fixed a small bug that would have occurred if the user clicked the
Download NFL Winners button, while it was halftime during one of the
If a user entered a player's picks that contained one or more
leading or trailing spaces, the toggle buttons didn't recognize this
pick (with the "invisible" space at the beginning or end) and wouldn't work
correctly. This is now fixed.
Added the User_Options worksheet tab, and all the logic behind it.
Increased the size of some of the variables that would have resulted
in errors with pools of more than 100 players.
The program now retrieves past game scores from
Allowed the user to enter the picks in any format (city name, mascot
name, etc.) they wish. The program will make the necessary
Many other updates too numerous to
Entered the entire 2014 schedule.
Added a few more checks to convert invalid team names
entered by the user into the proper two or three-letter
Another minor bug fix, related to the
2.06 bug fix. (When the spreadsheet was saved, closed, and then
re-loaded, the week wasn't always being updated correctly.)
Corrected another typo found in the 2013
Added a secret checksum routine, so that when a co-worker and I each
enter everyone's picks each week, we can instantly see if our
entries match, indicating we each made no mistakes, by now comparing
the checksum. If the checksums don't match, we know at least one of
us has made a mistake. (After loading the games, ATL-C displays the
Now that I've learned a little bit more
about Excel and VBA, I've cleaned up the code a bit. For example,
the user can now rename the six worksheet tabs, if desired,
and the program won't crash. (The names of the tabs were hard-coded
prior to this.) A few other internal enhancements / changes were
Found a typo (bug) when retrieving the data from prior weeks. The
saved scores weren't always being pulled in correctly. Fixed.
If your office pool uses Confidence Points, the program began
calculating which players are still alive when there were 11 games
(or less) remaining. However, with slower computers, 11 games is
still too many games... the program takes too long. (Excel is
slow......) This default is now 8 games and this number can now be
set by the user. (You do this above, by modifying the last two
characters in the DETERMINE WHO IS STILL ALIVE paragraph, on the
The program now automatically switches to full view mode upon
startup. (Hit ESC to get out of this mode.)
Very minor bugs found, when I spent some time and attempted
to make the program crash. Fixed. For this reason alone users should
download and use this version.
Last year our office pool didn't use the Monday Night Game
as a tie-breaker. We're doing so this year and I can already see a
few potential problems. For one, the program was sorting based upon
this tie-breaker... before the Monday night game has been played!
It's sorting on a 0-0 score! This is confusing and makes no
sense. Fixed. Now if your office pool is
using the Monday Night game as a tie-breaker, and if
this game has a score available, the program will then sort based
upon player score, tie-breaker, name. Otherwise it just sorts
based upon player score and then name.
Added this Version History section to
the program. (It currently just existed on the website.)
I didn't fully fix the previous bug. (And I'm not
convinced it's fully fixed now. More testing is called for.)
If you saved the worksheet, and then re-opened it, the
program didn't know how many players there were, and didn't update
the scores correctly.
Modified the program to now allow the user to enter
Confidence Points. (Good idea, Scott. Thanks.)
If the user selected a week that had no picks yet, when then
selecting another valid week, the font and formatting changed
Removed two buttons and three checkboxes. (The Main worksheet
tab was becoming too cluttered!)
Reformatted the Player_Picks worksheet tab a little bit.
Cleaned up some of the worksheet tabs. (Hid columns, removed
Excel headings, etc.)
Changed the initial week from 1 to 17. (Forcing the user to
change it back to 1 to pull in the data.)
Corrected a typo in the NFL schedule.
Updated with the entire 2013 schedule.
Whoo hoo! I just found out my calculator is being used
by someone else!
And they told me they had a problem, initially. They entered
the teams in uppercase, something my program didn't expect.
(Despite the fact that's exactly what my help file said to do.
Whoops.) Although they figured it out on their own, I
needed to fix this.
With Version 1.03, the user can now enter each player's picks in
uppercase or lowercase. The program will convert everything to the
proper case that is required when it comes time to load them from
the Main tab.
Modified the program to accept a TIE game as a possible
final result. (This past weekend, the Ram/49er game ended in a tie.)
The program was already capable of correctly determined your office
pool winner if a tie occurred. The user would simply not enter a
winner for that game - they would leave that toggle button alone, at
its initial "neutral" state.
The problem with that, however, was the program logic considered
that game was still had yet to be played... and thus the logic to
determine which players were still alive and which players were
mathematically eliminated wasn't giving correct results.
I'm a perfectionist... this wasn't acceptable to me, even though the
chances of another tie happening in the next several years is
This is now fixed.
To set a game as a tie, simply enter a "t" in the cell above the
toggle button (row 2) and then click on the toggle button.
With this version, the programming logic first looks to see if a "t"
is entered in that cell. If so, the "t" that was entered will be
erased and the program will mark that game as final, with a tied
result. Also, if you choose to set the toggle button results
automatically, by downloading the NFL results, the words TIE will
now appear in the toggle box, for all games that end in a tie.
Fixed a slight display glitch involving the Winner Toggle
Buttons. (At times, "n/a" was being displayed, and the button
wouldn't toggle, when it should have been able to.)
First release to the general public.
Added code to help check to make sure the names of the teams are
being input properly, in the Player_Picks worksheet tab.
Fixed a bug - the small "H" wasn't being displayed (to indicate
Halftime) above the toggle buttons, when downloading the scores
during halftime of a game.
Fixed a bug in the Cross_Reference chart tab, when displaying the
help message, which would have occurred during a week when no one
had a one-game difference with anyone else.
Added the cross reference chart tab.
Rewrote portions of the code, now that I'm becoming more familiar
with the syntax of Visual Basic.
Allowed the user to enter the weekly picks in ANY column order...
meaning, the games no longer have to sync up with the games as they
are entered on the NFL_Schedule tab.
Replaced the old NFL graphic with the graphic currently used by the
Added the code to save winners to a new tab, so the scores wouldn't
have to be downloaded again, each time that week was viewed.
Added the logic to accept and handle the total score in the Monday
night game, as a tie-breaker.
Turned off the display of headings and gridlines on
Rewrote the Help tab.
Repositioned most of the buttons.
Repositioned the title, week number, and number of weekly players in
the otherwise unused section of the upper-right corner.
Reduced some of the screen flicker when a few of the buttons were
Changed the week selector into a data validation drop-down box.
Cleaned up the rows - when a week is accessed that had fewer entries
than the previous week that was just viewed, empty rows are now
Changed the spreadsheet format - instead of 17 different tabs, there
are now just TWO tabs... one to hold all of the player picks and the
Main page which retrieves them.
Changed the buttons from Excel "Toggle"
Buttons to Excel "Command" Buttons.
Eliminated the need for the data in the hidden cells, in columns W
Color coded the buttons to match the home/visitor team, when
Coded the programming logic to retrieve past scores, for
the logic to download the score of the games from the NFL website.