Digital YAHTZEE.xls

You may have seen my Reusable Yahtzee Score Sheet and know that I really like to play. But I have not had the time to play much. So I started toying with the idea of a digital YAHTZEE. Something I could open on my computer at anytime. At first I made a couple of variations in .vbs, but it lacked pizzazz, so I looked to Excel. What .vbs lacked in functionality Excel.... well excelled. Here is the fruit of my labor. I hope you enjoy creating and playing your very own Digital YAHTZEE.xls.

Custom Features:
Conditional Formatting - text and background changes of font and color, conditional to the value in the cell.
Running Tally - adding the value of cells to calculate the total.
Rolling Dice - the dice give the illusion that you have just rolled them.
Dice Grouping - the dice will line up in numerical order. Which means if you keep the 2nd and 3th die because their values are 2 and 2; the next roll will put the new rolled dice values in numerical order around the 2s. e.g. 1st roll 1, 2, 2, 4, 6. You kept the 2 and2 and rolled again receiving a 2, 5, 6. The dice will line up as 2, 2, 2, 5, 6.

In the interest of keeping this as simple as possible, I uploaded a basic score sheet that will give you Game 1 with conditional formatting as well as the row tracker which alerts you to your status of reaching the bonus point for the Upper Section. To finish your Digital YAHTZEE.xls you will need to create the dice, Total and Roll Tracker, the 4 buttons and Games 2 - 4 (instructions included).

Note: There are a lot of details in this instructable. Some of the steps will have a .gif so the page may be slow to load. Once the .gifs load they will be beneficial to assist you in creating your Digital YAHTZEE.xls.

So without further ado, I submit for your dice playing privilege Digital YAHTZEE.xls.

Step 1: Create the Score Sheet

Create the basic score sheet.

1. Open Excel or download the basic score sheet.(Excel 97-2003 format)
2. Save the file as .xls (Excel 97-2003 format) or .xlsm (Excel Macro-Enabled Workbook for Excel 2007 or greater) or
3. If you decide not to download the basic score sheet then start creating the score sheet; column by column and row by row.

Step 2: The Code

1. Click on the Developer tab.
2. Click on the Visual Basic icon.
3. Double click on Sheet1() then copy and paste the below code into the "Sheet 1".

Sub Randomizer()
Dim n As Long, i As Byte, x As Byte
Dim Box As Range
Dim count As Range

Set count = [P3]
If count > 0 Then
count.Value = count.Value - 1

Set Box = Range("K7")
If IsEmpty(Sheet1.Range("K7")) Then
' this will call the Randomize Function
Call Run_Randomize(Box)
End If

Set Box = Range("K8")
If IsEmpty(Sheet1.Range("K8")) Then
' this will call the Randomize function
Call Run_Randomize(Box)
End If

Set Box = Range("K9")
If IsEmpty(Sheet1.Range("K9")) Then
' this will call the Randomize function
Call Run_Randomize(Box)
End If

Set Box = Range("K10")
If IsEmpty(Sheet1.Range("K10")) Then
' this will call the Randomize function
Call Run_Randomize(Box)
End If

Set Box = Range("K10")
If IsEmpty(Sheet1.Range("K10")) Then
' this will call the Randomize function
Call Run_Randomize(Box)
End If

Set Box = Range("K11")
If IsEmpty(Sheet1.Range("K11")) Then
' this will call the Randomize function
Call Run_Randomize(Box)
End If

' Message when you push the roll dice button more than 3 times.
ElseIf count = 0 Then
MsgBox " Push the ''Clear All Dice'' button." & Chr(10) & " Then push ''Roll Dice''.", , " Sorry, but your is Turn Over!"
Exit Sub
End If
' ************ ' Sort Dice Numerical - works in column only
Dim oneRange As Range
Dim aCell As Range

Set oneRange = Range("K7:K11")
Set aCell = Range("K7")

oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlNo
' ************
End Sub

Sub Run_Randomize(Box As Range)
' This code will produce a random number on the die from 1 - 6 after the 10th time, 'similar to shaking your hand 9 times and releasing the die on the 10th before the actual number appears on the die
Randomize
For n = 1 To 10
Randomize
For i = 1 To 1
x = 1 + Int(Rnd * 6)
Box(1, 1) = x
Next i
Next n
End Sub

Sub ClearReset_Click()
'Clear Dice & reset Roll Counter
Range("K7:K11").ClearContents
'Range("J12:V12").ClearContents
Range("P3").Value = "3"
End Sub

Sub Reset_Click()
' Clears Score Card
' Upper Section
Range("c2:c7").ClearContents ' Game 1
Range("E2:E7").ClearContents ' Game 2
Range("G2:G7").ClearContents ' Game 3
Range("I2:I7").ClearContents ' Game 4
' Lower Section
Range("c13:c19").ClearContents ' Game 1
Range("E13:E19").ClearContents ' Game 2
Range("G13:G19").ClearContents ' Game 3
Range("I13:I19").ClearContents ' Game 4
' Reset Roll Counter
Range("P3").Value = "3"
' Reset Dice
Range("K7:K11").ClearContents
End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' If Range1 is within Range2 then True
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function

Sub Die1()
' Clear contents of selected cells
If InRange(ActiveCell, Range("k7:k11")) Then
' Clear Contents
Selection.ClearContents
Else
' Do nothing
Exit Sub
End If
End Sub

'Code be quick, code be written. 5 Dice I envision.

Step 3: The 1st Die

Watch the above .gif as a visual aid which follow the below steps.

1. Highlight cells K7 - K11 and format Font Style as "Bold", Text Alignment Horizontal to "Center" and Text Alignment Vertical to "Center" and click "All Borders".
2. Highlight cells across from L8 - N8 and down L10 - N10. This will be your first die.
3. Create an "Outside Border" around those cells by choosing "Thick Box Border" in Borders from the drop down list and Width 4.29.
4. Format all of the cells with Font of "Webdings", Font Style as "Bold" and Size as "11",
5. Highlight cell L8 then paste =IF(AND($K7>=2,$K7<=6)," l "," ") into the cell.
6. Move to cell N8 then paste =IF(AND($K7>=4,$K7<=6)," l "," ")
7. Move to cell L9 then paste =IF($K7=6," l "," ")
8. Move to cell M9 then paste =IF(OR($K7=1,$K7=3,$K7=5)," l "," ")
9. Move to cell N9 then paste =IF($K7=6," l "," ")
10. Move to cell L10 then paste =IF(AND($K7>=4,$K7<=6)," l "," ")
11. Move to cell N10 then paste =IF(AND($K7>=2,$K7<=6)," l "," ")
12. Move to cell K7 and enter any number from 1 - 6, this will test if the number in cell K7 matches the dot locations you would see on the die.

The 1st die is done; now let's make 4 more times the fun.

Step 4: The 2nd Die

Watch the above .gif for a visual aid which follow the below steps. Note: Dice 2 - 5 are functionally similar to create. You copy and paste the previous die and link the code to the new cells.

13. Highlight columns L,M,N; copy and paste them on column O,P and Q.
14. Move to cell O8. In the Formula Bar, click anywhere in the Formula. You will see the border of cell "K7" change color. Move your mouse so that cell "K7" becomes bold. Click and drag the border down to cell "K8". The formula will change from "=IF(AND($K7>=2,$K7<=6)," l "," ")" to "=IF(AND($K8>=2,$K7<=6)," l "," ")". Notice the 1st cell listed has changed to "K8", change the 2nd cell listed to "K8".
15. Move through each cell of the 2nd die changing "K7" to "K8". Once that is done cell, "K8" will be where the 2nd die will be populated from.
16. Move to cell "K8" and enter any number from 1 - 6, this will test if the number in cell "K8" matches the dot locations you see on the die.

Count with me 1 + 1 equals 2, there are only 3 more dice to accrue.

Step 5: The 3rd Die

Note: Dice 3 - 5 are functionally similar to create. You copy and paste the previous die and link the code to the new cells.

17. Highlight columns O,P,Q; copy and paste them on column R,S and T.
18. Move to cell "R8". In the Formula Bar, click anywhere in the Formula. You will see the border of cell "K8" change color. Move your mouse so that cell "K8" becomes bold. Click and drag the border down to cell "K9". The formula will change from "=IF(AND($K8>=2,$K8<=6)," l "," ")" to "=IF(AND($K9>=2,$K8<=6)," l "," ")". Notice the 1st cell listed has changed to "K9", change the 2nd cell listed to "K9".
19. Move through each cell of the 3rd die changing "K8" to "K9". Once that is done cell, "K9" will be where the 3rd die will be populated from.
20. Move to cell "K9" and enter any number from 1 - 6, this will test if the number in cell "K9" matches the dot locations you see on the die.

Once, twice and now thrice, there’re only 2 more dice.

Step 6: The 4th Die

Note: Dice 4 - 5 are functionally similar to create. You copy and paste the previous die and link the code to the new cells.

21. Highlight columns R,S,T; copy and paste them on column U,V and W.
22. Move to cell "U8". In the Formula Bar, click anywhere in the Formula. You will see the border of cell "K9" change color. Move your mouse so that cell "K9" becomes bold. Click and drag the border down to cell "K10". The formula will change from "=IF(AND($K9>=2,$K9<=6)," l "," ")" to "=IF(AND($K10>=2,$K9<=6)," l "," ")". Notice the 1st cell listed has changed to "K10", change the 2nd cell listed to "K10".
23. Move through each cell of the 4th die changing "K9" to "K10". Once that is done cell, "K10" will be where the 4th die will be populated from.
24. Move to cell "K10" and enter any number from 1 - 6, this will test if the number in cell "K10" matches the dot locations you see on the die.

With 4 dice in the review, only 1 more die to do.

Step 7: The 5th Die

Note: This die is functionally similar to create as the previous 3 dice. You copy and paste the previous die and link the code to the new cells.

24. Highlight columns U,V,W; copy and paste them on column X,Y and Z.
25. Move to cell "X8". In the Formula Bar, click anywhere in the Formula. You will see the border of cell "K10" change color. Move your mouse so that cell "K10" becomes bold. Click and drag the border down to cell "K11". The formula will change from "=IF(AND($K10>=2,$K10<=6)," l "," ")" to "=IF(AND($K11>=2,$K10<=6)," l "," ")". Notice the 1st cell listed has changed to "K11", change the 2nd cell listed to "K10".
26. Move through each cell of the 5th die changing "K10" to "K11". Once that is done cell, "K11" will be where the 5th die will be populated from.

All 5 dice are ready but before we connect the dots lets add some other features...

Step 8: Total and Roll Tracker

1. Highlight cells N2 through P3.
2. Click Outside Borders.
3. Highlight cells N2 - P2 and click Outside Borders.
4. Highlight cells P2 - P3 and click Left Border and format Font Style as "Bold", Text Alignment Horizontal to "Center" and Text Alignment Vertical to "Center".
5. Move to cell N2 and type "Total=".
6. Format Font Style as "Bold", Text Alignment Vertical to "Center".
7. Move to cell P2 and paste =SUM(K7,K8,K9,K10,K11)
8. Move to cell N3 and type "Roll" then move to P3 and Type "3" (this is the number of rolls you have left).
9. Now the Total and Roll Tracker is done.

The next step is to add some buttons....

Step 9: Button 1

There are 4 buttons:
1. Roll the dice
2. Clear the dice and reset the Total and Roll counter
3. Reset the score sheet and
4. Remove the unwanted die

Button1
1. Click on the Developer tab.
2. Click on the Insert icon.
3. Click on the button icon within the Form Controls section. Note: When you click on the button icon and move the mouse to where you want to place the button, all you will see are cross hairs. When you click on the spreadsheet you will see the button.
4. Move the cursor to cell K5 and click on the spreadsheet, it can be moved later.
5. In the Assign Macro dialog box click on Macro Name "Sheet1.Randomizer" then click "OK".
6. Highlight "Button 1" and type "Roll Dice" to rename the button.

No need to fuss or make a to-do, the next step is simply create Button 2.

Step 10: Button 2

1. Click on the Developer tab.
2. Click on the Insert icon.
3. Click on the button icon within the Form Controls section. Note: When you click on the button icon and move the mouse to where you want to place the button, all you will see are cross hairs. When you click on the spreadsheet you will see the button.
4. Move the cursor to cell M5 and click on the spreadsheet, it can be moved later.
5. In the Assign Macro dialog box click on Macro Name "Sheet1.ClearReset" then click "OK".
6. Highlight "Button 2" and type "Clear All Dice" to rename the button. Note: You will have to resize the button to fit the name. Click on the grey box around the button and drag outward to resize.

There have been a lot of steps would you agree, keep on going and create Button 3.

Step 11: Button 3

1. Click on the Developer tab.
2. Click on the Insert icon.
3. Click on the button icon within the Form Controls section. Note:When you click on the button icon and move the mouse to where you want to place the button, all you will see are cross hairs. When you click on the spreadsheet you will see the button.
4. Move the cursor to cell P5 and click on the spreadsheet, it can be moved later.
5. In the Assign Macro dialog box click on Macro Name "Sheet1.Reset_Click" then click "OK".
6. Highlight "Button 3" and type "Reset Board" to rename the button. Note: You will have to resize the button to fit the name. Click on the grey box around the button and drag outward to resize.

Are you ready for more, do not stop now create Button 4.

Step 12: Button 4

1. Click on the Developer tab.
2. Click on the Insert icon.
3. Click on the button icon within the Form Controls section. Note: When you click on the button icon and move the mouse to where you want to place the button, all you will see are cross hairs. When you click on the spreadsheet you will see the button.
4. Move the cursor to cell L7 and click on the spreadsheet, it can be moved later.
5. In the Assign Macro dialog box click on Macro Name "Sheet1.Die1 then click "OK".
6. Highlight "Button 4" and type "Remove Selected Die" to rename the button. Note: You will have to resize the button to fit the name. Click on the grey box around the button and drag outward to resize.

Now that the dice and the buttons are in place, let’s add the How to Play instructions to our knowledge base.

Step 13: How to Play

Here are the instructions on how to play.

1. Copy and paste the instructions, below the line of asterisks, in cell L14.
2. Format cell L14 Font Style as "Bold", Text Alignment Horizontal to "Center".
3. Format cell L16 Text Alignment Horizontal to "Center".
4. Format cell L17 Text Alignment Horizontal to "Center".
5. Highlight cells L14 - AA22 then click Outside Borders.

************************************************

How to play your Digital Yahtzee:
1. Push the "Roll Dice" button.
2. Highlight the cell(s) between K7 and K11 to remove the ones you do not want to keep.
3. Push the "Remove Selected Die" button to remove.
4. Push the "Roll Dice" button to roll again.
5. Highlight the cell(s) between K7 and K11 to remove the ones you do not want to keep.
6. Push the "Remove Selected Die" button to remove.
7. Push the "Roll Dice" button to roll again, this is your last roll.
8. Enter the results on the Score Sheet that the dice best represent.

Dice, buttons and How to Play, let's add Game 2, Game 3 and Game 4 to average out the score.

Step 14: Games 2 - 4

To add Games 2 - 4:
1. Highlight columns C and D.
2. Paste in column E.
3. Highlight columns C, D, E, F and paste on Column G.
4. Change column header from Game "1" in column "E" to "2", column "G" to "3" and column "I" to "4".

You are done! You can now play your Digital YAHTZEE.xls.

Step 15: Observations & Summary

Now you have a functional Digital YAHTZEE.xls. You can add background colors, more conditional formatting or other features to customize your game. Watch the above .gif to see my Digital YAHTZEE.xls in action.

Observations
1. Even though this is set up as a single player venue, you can use Games 2 - 4 for additional players.
2. Since creating my Digital YAHTZEE.xls my overall scores have increased.
3. There are no measures to prevent anyone from cheating, but there is delight in honesty.

Summary
I am satisfied with my Digital YAHTZEE.xls. I play a game a couple of times a week. It is a nice break.

Then I saw, and considered it well. I looked upon it, and received instruction..