Customizable trivia game tutorial for family game night, workplace teambuilding and more

This is customizable trivia game for 2-9 players. All of the topics and questions + answers can be modified. There is also an optional timer. See the "Instructions" worksheet with how to play and how to modify. Below is how the game operates.

Non-VBA
Cell References
Let's start with the non-VBA part of the workbook. The players' names or team names are entered into Row 1. Each wedge charts' title is a reference to the player's name. For wedge chart 2, the title is =Trivia!$D$1:$E$1 which by default is "Team 2".

Beginning with the player's scores in rows 2-7, for each topic column, there is simply a reference to the Legend in column "V". When the legend Topics are changed, the player's topic are also changed. The worksheets (tabs) at the bottom are changed with VBA documented later.

Conditional Formatting
Row 1 contains the player's names. The coloring of this row uses conditional formatting to display the current player and winner. If all the answer column for that player is non-empty, it turns red. If the player is the current player, it turns green. The current player is stored on a hidden worksheet called "Hidden."


For each answer there is a conditional formatting where any character put in that cell changes color. For the game, a SPACE is put there because it is invisible. But any character will do.


Hidden Items
There are three areas where information is hidden from the players and moderator. First, there is a hidden worksheet called "Hidden." It contains a number of variables that are used to track the gameplay. There is a cell on Hidden that contains the current player number. Another holds the topic the player chose. There is also a count of the number of questions for each topic. This is used by the random number generator as the maximum value. Finally, there is data used by the wedges in the pie chart. There are six "1" values in that column to make 6 wedges. Excel does not have a way to fill in individual wedges of a pie chart, so this data allows us to build our own pie charts.

You will notice column A is hidden. This was left over from a previous iteration of the game and are not used anymore. It was more work than it was worth to move the game to start in column A.

Finally, Column C on each Topic worksheet is also hidden. This contains a flag if the question was used before so that questions are not repeated.

Data Validations
Finally, on the Instructions worksheet, the number of players and the timer input have data validations to ensure the proper values. 2-9 for the number of players on worksheet Instructions in cell B18.


And the number of seconds for a timer, 0-600, on worksheet Instructions in cell B21.

VBA
Module1
Module1 contains 4 global variables used to pass data to and from the Q&A form. These are the question text, the answer text and if the player got the answer right or wrong.

Global Answer As String
Global Question As String
Global Correct As Boolean
Global Wrong As Boolean

Sheet1 (Trivia)
When any of the six question buttons are pressed, they each call their own TopicxButton_Click() sub.

Private Sub Topic1Button_Click()
    QAForm.Caption = Worksheets("Trivia").Range("$V$12") + " Question"
    showQuestionBox (topic1)
End Sub

Each topic button sets the caption of the question and answer form and calls the showQuestionBox() sub with their topic number as the parameter.

showQuestionBox() is the subroutine that controls the gameplay. It is responsible for generating the random question, showing the Question and Answer form, deciding what to do if the question is answered correctly, wrong or the timer expires and controlling which players turn it is.

showQuestionBox() generates a random number, goes to the proper topic worksheet and pulls the question and answer from that random row. Then shows the question and answer form. The form loads the global Booleans Correct and Wrong with 4 combinations.

    ' Wrong=True and Correct=True means timer ran out
    ' Wrong=True and Correct=False means wrong answer was given
    ' Wrong=False and Correct=True means correct answer was given
    ' Wrong=False and Correct=False means question was repeated

The repeated question option could be removed since we track which questions have been used and select a different random number until an unused one is picked. If the player answered correctly, put something in the box next to their topic and move to the next player. If wrong or time ran out, just move to the next player.

Once we move to the next player, the showQuestionBox() sub then disables button that the player has already answered correctly. Finally, ThisWorkbook.trivia() updates the screen.

Finally, we monitor the topics in the legend to see if they change. If one of them changes, we update the button and the name of the worksheet to match it.

    If Target.Address = "$V$12" Then                    
        Worksheets(3).Name = Range("v12").Value
        Topic1Button.Caption = Range("v12").Value + " Question"  
    End If

Sheet2 (Instructions)
We need to keep track of whether the number of players has changed. We watch to see if cell B:18 changes and if so, call NumPlayersChanged()

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$18" Then
        Call NumPlayersChanged
    End If
End Sub

The NumPlayersChanged() sub hides the charts and changes the font color to white (invisible) for the unused players.

ThisWorkbook
This contains 2 subs. Clearall() resets all value to start a new game. It is called when the Clear All button is pressed. The other sub is trivia() and it manages filling in the wedges and determining the winner.

As mentioned, Excel does not have a built in way to fill in a wedge. For player1, their chart is named Chart1. We look at their cells C2 - C7. If that cell is not blank, we fill in that wedge by changing the color of the Points(wedge num).

        
        chartname = "Chart " & player                              'set the pie graph number
    
        ActiveSheet.ChartObjects(chartname).Activate        'active that pie graph
        ActiveChart.ChartArea.Select

        For rownumber = 2 To 7 
            col = (player * 2) + 1
            If Cells(rownumber, col) <> "" Then
                ActiveChart.SeriesCollection(1).Points(rownumber - 1).Interior.Color = colors(rownumber - 2)
            Else
                ActiveChart.SeriesCollection(1).Points(rownumber - 1).Interior.Color = white
            End If
        Next

QAForm Code
This is the form that appears and shows the questions. If the player answers correct, wrong or repeated question, the form is hidden, values are reset and Correct and Wrong set. It is straight forward except the timer. If there is a timer value on the Instructions worksheet, we need to call CountdownTimer(). First, it changes the number of seconds to hh:mm:ss format for display in the upper right corner of the form.

We remember the current time as the TimerStart. If TimerStart plus the length of the time are greater than now, keep looping.

Loop While ((TimerStart + SecondsToRun > Timer) And (QAForm.Visible))

If the form is still visible, no one pushed the Correct, Wrong or Repeat buttons, the timer must have expired. If it did, show the correct answer for 5 seconds and then hide the form.

If (QAForm.Visible) Then 
        AnswerBox.Text = Answer
        Correct = True
        Wrong = True 
        CorrectButton.Enabled = False
        Application.Wait (Now + TimeValue("0:00:05")) 
        Hide                                                            
 End If