The main explanation text for this article is in black and bold font. This is the primary procedure used in xlCubeSuper. When a cell is clicked, something happens depending on where it is and what it contains. The original documentation for this procedure is highlighted in green and other procedures used are highlighted in blue. They will be discussed in Part6.
“Shooting” is accomplished by this event procedure.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Over Then Exit Sub ‘the Over variable is set to True when a game is finished
If ActiveSheet.Name = “Scores” Then Exit Sub ‘just in case you want to unhide this sheet
If Target.Font.ColorIndex > 1 Then Exit Sub ‘it already contains an “X”
If ActiveCell.Address <> Target.Address Then Exit Sub ‘if it is not a single cell selection
If Intersect(Range(“board”), Target) Is Nothing Then Exit Sub ‘if the selection is not on the board
Each of the 5 previous If-Then statement handle situations where the shot is not “correct”.
NumOfHits = Application.WorksheetFunction.Sum(Range(“sumofhits”).Value) ‘hits before entry
On Error GoTo TheEnd
Target.Value = 1 ‘entry is made
This is prehaps the most important part of this procedure, although it looks very simple. It is, but adding a 1 to the cell does several things. First, it allows the formulas described in Part4 to keep track of all of the shots and hits. So, how does an X appear on the game board? Each cell has a custom format of “X”,,, which shows an X in the cell no matter what is entered.
fShtPos = 0
TheRange = “”
The following loop checks to see whether the active cell is where any of the 7 centers of the cubes is located. If so, it will disable the hint in the menu associated with that cube and place a blue at that spot. Next, if the game is over (z=7) any hint costs will be summed and then added to the curent # of shots.
For p = 1 To 7
On Error Resume Next
tdRangeParse “centerShip” & p
If ActiveSheet.Name & Target.Address = fShtPos & TheRange Then ‘if a center of a cube has been hit
Target.Font.ColorIndex = 32
MsgBox “You just destroyed xlCube” & p & “!”, , “DESTROYED!”
Names(“cShip” & p).RefersTo = Range(“sums” & p).Value ‘changes name from reference to a value (# of hits)
Names(“centerShip” & p).Delete
Set cBar = Application.CommandBars.FindControl(Tag:=”Hint” & p)
cBar.Enabled = False ‘disables hint since corresponding cube is gone
For z = 1 To 7
If Mid(Names(“cShip” & z).RefersTo, 2, 1) = “‘” Then Exit For
If z = 7 Then
pScore = hint1Val + hint2Val + hint3Val + hint4Val + hint5Val + hint6Val + hint7Val ‘sums penalty score for hints
sScore = Range(“x15”).Value
fScore = pScore + sScore
MsgBox “The game is over! Your score is ” & fScore & “. ” & Chr(10) & Chr(13) & Level(fScore), , “GAME OVER!”
ThisWorkbook.Save ‘saves changes made to records table
If NumOfHits < Application.WorksheetFunction.Sum(Range(“sumofhits”).Value) Then
Target.Font.ColorIndex = 3 ‘turns red if hits > before event
Finally, if the the shot is a hit, it will change the font color to red.
Although I have not explained every aspect of this procedure, I hope that this helps!