Updated: May 28, 2021
In Excel you can program actions to happen when the user left, right or double clicks the mouse. In this tutorial we use that to build a user interface for nonograms.
The downloadable workbook contains all the formatting required, and we take you step-by-step through the code required. Additional exercises are set that will further stretch your learning.
Nonogram Player workbook
The starting point for this tutorial is an accompanying workbook. It contains a puzzle and all the formatting necessary. It contains no macros, so there will be no security warning when you download it, and you will be the one entering the code and enabling macros.
Download and open the workbook by clicking the download icon, above
Open the downloaded workbook.
Note the presence of Named Ranges for, "RowClues", "ColClues", "Grid", and "Home"
Note (and test) that Conditional Formatting has been set whereby cells in the Grid:
with values of 1 are shown as blank;
with values of 2 are shown as filled; and
with values of 3 are shown as crossed
Save a copy of the Nonogram Player as a Macro-enabled Workbook.
Launch Microsoft Visual Basic for Applications
VBA code is written in 'Microsoft Visual Basic for Applications'. This can be launched by clicking the Visual Basic button in the developer tab, which is not visible by default, but can be switched on by customising the ribbon (File > Options), but it is also available with a keyboard shortcut, which we can use now.
Use [Alt]+[F11] to open VB window
Use [Ctrl]+[R] to show project explorer
Right click "Sheet 1 (Nonogram)"
Select the View Code option.
This is where all the code for this tutorial will be added.
Arrange the windows on your screen so that you can see both the Excel workbook and the coding window. If your screen is big enough it would also help to be able to see this tutorial page.
Numbers have meaning
The numbers that govern the conditional formatting in the Grid must be the same numbers that are understood by the code we're about to create, and this is achieved with the following 'enumeration' code.
Enum CellValue blank = 1 filled = 2 crossed = 3 End Enum
Insert the above code below the, "Option Explicit", line.
At the top of the worksheet there are two drop-down boxes.
In the left drop-down
In the right drop-down
select, "SelectionChange"; then
Select "BeforeDoubleClick"; and finally
This will create three blank event handlers, and the first thing we will do is add between the header and End statement of each event handler, a simple debug statement to help us understand what is happening and when.
Enter the following into the body of the Worksheet_BeforeDoubleClick event handler:
Debug.print "Double Click", Target.Address
Enter the following into the body of the Within Worksheet_BeforeRightClick event handler:
Debug.print "Right Click", Target.Address
Enter the following into the body of the Within Worksheet_SelectionChange event handler:
Debug.print "Selection Change", Target.Address
The coding window should now appear as follows:
Debug statements are output to the, "Immediate Window", in which a new line will now appear when any of the above three events occur.
In the coding window use the shortcut [Ctrl]+[G] to show the 'immediate window'.
Undertake the following actions in the workbook, observing the immediate window.
Left-click a cell
Right-click a different cell
Double-click a third cell
Use an arrow keys on your keyboard to select a fourth cell
Left-click a cell and then (slowly to avoid a double click) left click it again.
Right-click a cell; and then right-click it again.
Double-click the copyright text; press escape; and then double click same cell.
Select multiple cells and then double-click one of the cells within.
Select multiple cells and then right-click one of the cells wuthin.
From watching the immediate window you should see that:
The SelectionChange event handler is only fired when the selection changes, which is not every time the left mouse button is clicked.
The SelectionChange event handler is also fired before the Double-Click and Right-Click events, whenever those events are on a different cell to the one selected.
A left-click presents a context sensitive menu.
A double-click allows the editing of a cell's content, rather than its replacement.
A double-click always reduced the current selection to a single cell; but a right-click is valid for a multi-cell selection.
These are the rules of Excel that will both help and hinder our endeavour.
Left click to fill (or blank) cell
With a little trick, which we'll come to, we can use the Selection Change event handler to detect left mouse clicks.
If target.CountLarge > 1 Then: Exit Sub If Intersect(target, Range("Grid")) Is Nothing Then: Exit Sub Select Case target.Value Case CellValue.filled: target.Value = CellValue.blank Case Else: target.Value = CellValue.filled End Select Range("Home").Select
Add the above code into the SelectionChange event handler, below the debug statement, and note the following:
The Target parameter identifies the cell (or cells) that have been selected. We are only interested in making a change when one cell is selected. So the first line of code ensures we only act when a single cell has been selected,
Intersect identifies any overlap between the selection and the Grid. The second line of code ensures we only act when a Grid cell has been selected.
We now act to change the value of the target cell to, "Filled", or back to, "Blank", if already filled. The Select block of code does this.
Range("Home"). Selects the, "Home", named range, which you will remember is the single cell at 'J12'. This is the trick that ensures, should the user left click again on the same cell, the SelectionChange event will fire and we will toggle the cell value again.
Execute the following tests:
Left-click individual cells both inside and outside of the grid.
Select multiple cells inside and outside the grid.
Select multiple cells where some are inside and some are outside.
Move the selection using the arrows on your keyboard.
Left-click the same cell in the grid twice or more.
Right click to cross (or blank) cell
Normally in Excel, when the user right-clicks, a right-click menu appears. The BeforeRightClick event happens before the menu appears, providing us with the opportunity to add functionality of our own and, optionally, prevent the right-click menu from appearing.
If target.CountLarge > 1 Then: Exit Sub If Intersect(target, Range("Grid")) Is Nothing Then: Exit Sub Select Case SavedValue Case CellValue.crossed: target.Value = CellValue.blank Case Else: target.Value = CellValue.crossed End Select Cancel = True
Add the above code into the BeforeRightClick event handler, below the debug statement, and note the following differences to the code in the SelectionChange event handler:
The, "Cancel = True", line instructs Excel not to present the right-click menu, which is the normal processing that would follow this event.
Whereas the SelectionChange event dealt with filled cells, this one deals with crosses.
There is no need to insert code to select the "Home" cell. This is because the SelectionChange event handler, called prior to this event, will already have done this.
Because the SelectionChange event has already run, it will have modified the cell value, which means we don't want to base our decision on the current value of the target, but on the value of the target before it was changed, which will be, "SavedValue", which we will now create.
Add the following code at the top of the module, just below the 'End Enum' statement:
Add the following code into the SelectionChange event, just above the 'Select' statement:
SavedValue = Target.Value
Execute the following tests:
Right-click individual cells both inside and outside of the grid.
Select multiple cells inside the grid and then right-click one of them.
Right-click one cell in the grid twice or more.
Double Click to change colour of clues
On double clicking a cell, Excel normally allows to edit the contents rather than overwrite. But in this example, when the user double clicks on a clue, we want it to change to red or, if already red, back to black. Marking clues in this way helps the user keep track of which clues have been completed and which have not.
If Intersect(target, Range("RowClues")) Is Nothing _ And Intersect(target, Range("ColClues")) Is Nothing Then: Exit Sub With target.Font If .Color = vbBlack Then .Color = vbRed Else .Color = vbBlack End If End With Cancel = True
Add the above code into the BeforeDoubleClick event handler, below the debug statement, and note the following differences to the code in the BeforeRightClick event handler:
There is no need to check the number of cells in the target range, because double-click always selects a single cell.
Two ranges (RowClues and ColClues) are checked rather than one, and only if both intersects are empty is the subroutine exited. Note the underscore which acts as a continuation character.
Instead of changing the value of the clue, this process is concerned with the changing the colour of the font.
Execute the following tests:
Double-click clues inside and outside RowClues.
Double-click clues inside and outside ColClues,
Double-click the same clue twice or more.
You may wish to undertake the following optional assignments.
Assignment 1: Review Usability
Try to solve the Nonogram using left, right and double clicks and ask yourself the following questions:
How does the flickering (caused by selection of Home) diminish from the experience?
What happens when you use keyboard arrows to navigate into the Grid and, if you don't like the behaviour, can you think of a way to fix it?
Would it be better to use left-click instead of double click to mark clues as done?
Assignment 2: Create Further Nonograms
There are plenty of online nonograms of different sizes. You may duplicate the 'Nonogram' worksheet by: right-clicking on it's tab; selecting the Move or Copy option; and ticking the Create a copy checkbox. You can then add rows and columns as required, making sure to add them into the middle of the grid, and not the extremities (thereby retaining the conditional formatting and named ranges). However, you may have problems entering clues and other tasks, due to the fact that the normal Excel functionality has been overwritten
Can you think of a way to allow the temporarily switch off your code so that you can more comfortable edit the worksheet?
You will also note that all the code you wrote for the Nonogram worksheet, will be duplicated in the copies you create. This does mean that the new sheet will work without little effort, but also means that there are multiple copies of the same code, and that any code changes will need to be replicated for each nonogram.
Can you create a new module containing the bulk of the code, written once, and minimise the code in each worksheet that gets replicated?