+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    12-13-2022
    Location
    Duluth, GA
    MS-Off Ver
    365
    Posts
    7

    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.

    Name:  Screenshot 2023-01-26 072244.jpg
Views: 13
Size:  237.8 KB

    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."
    Name:  Picture1.png
Views: 34
Size:  36.3 KB

    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.
    Name:  Picture2.png
Views: 30
Size:  28.8 KB

    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.
    Name:  Picture3.png
Views: 33
Size:  8.6 KB

    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.
    Please Login or Register  to view this content.
    Sheet1 (Trivia)
    When any of the six question buttons are pressed, they each call their own TopicxButton_Click() sub.
    Please Login or Register  to view this content.

    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.

    Please Login or Register  to view this content.
    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.
    Please Login or Register  to view this content.

    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()
    Please Login or Register  to view this content.

    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).

    Please Login or Register  to view this content.
    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.
    Please Login or Register  to view this content.
    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.
    Please Login or Register  to view this content.
    Last edited by tj4242; Today at 03:28 PM.

  2. #2
    Registered User
    Join Date
    12-13-2022
    Location
    Duluth, GA
    MS-Off Ver
    365
    Posts
    7

    Re: Customizable trivia game tutorial for family game night, workplace teambuilding and mo

    I made some tweaks. I removed some VBA code and replaced it with conditional formatting. And it seems some of the conditional formatting for Topic 3 (row 4. In the example topic, the Excel row) was not correct. That has been fixed.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Golf game: Banker Game
    By Clark_Griswold in forum Excel General
    Replies: 1
    Last Post: 06-23-2021, 11:41 AM
  2. New to the game
    By bigmouth0317 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 10-27-2014, 09:12 AM
  3. Home brewer, markman, game designer, family man. Mixing technology and magic.
    By youngtusk87 in forum Hello..Introduce yourself
    Replies: 3
    Last Post: 09-17-2012, 04:47 AM
  4. The Man Game Help
    By Darkrogueseal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2010, 07:20 PM
  5. The Man Game Help
    By Darkrogueseal in forum Excel General
    Replies: 5
    Last Post: 05-03-2010, 05:45 PM
  6. [SOLVED] Game score without game being played
    By Sheila in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-17-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1