top of page
Search

Program Mouse Clicks in Excel

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.

Nonogram Player
.xlsx
Download XLSX • 20KB

  1. Download and open the workbook by clicking the download icon, above

  2. Open the downloaded workbook.

  3. Note the presence of Named Ranges for, "RowClues", "ColClues", "Grid", and "Home"

  4. Note (and test) that Conditional Formatting has been set whereby cells in the Grid:

  5. with values of 1 are shown as blank;

  6. with values of 2 are shown as filled; and

  7. with values of 3 are shown as crossed

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

  1. Use [Alt]+[F11] to open VB window

  2. Use [Ctrl]+[R] to show project explorer

  3. Right click "Sheet 1 (Nonogram)"

  4. Select the View Code option.

This is where all the code for this tutorial will be added.

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

 

Event Handlers

At the top of the worksheet there are two drop-down boxes.

  1. In the left drop-down

  2. Select, "Worksheet"

  3. In the right drop-down

  4. select, "SelectionChange"; then

  5. Select "BeforeDoubleClick"; and finally

  6. Seect, "BeforeRightClick"

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.

  1. In the coding window use the shortcut [Ctrl]+[G] to show the 'immediate window'.

  2. Undertake the following actions in the workbook, observing the immediate window.

  3. Left-click a cell

  4. Right-click a different cell

  5. Double-click a third cell

  6. Use an arrow keys on your keyboard to select a fourth cell

  7. Left-click a cell and then (slowly to avoid a double click) left click it again.

  8. Right-click a cell; and then right-click it again.

  9. Double-click the copyright text; press escape; and then double click same cell.

  10. Select multiple cells and then double-click one of the cells within.

  11. 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:

  1. 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,

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

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

  4. 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:

  1. Left-click individual cells both inside and outside of the grid.

  2. Select multiple cells inside and outside the grid.

  3. Select multiple cells where some are inside and some are outside.

  4. Move the selection using the arrows on your keyboard.

  5. 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:

  1. The, "Cancel = True", line instructs Excel not to present the right-click menu, which is the normal processing that would follow this event.

  2. Whereas the SelectionChange event dealt with filled cells, this one deals with crosses.

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

  4. 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:

Dim SavedValue

Add the following code into the SelectionChange event, just above the 'Select' statement:

SavedValue = Target.Value

Execute the following tests:

  1. Right-click individual cells both inside and outside of the grid.

  2. Select multiple cells inside the grid and then right-click one of them.

  3. 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:

  1. There is no need to check the number of cells in the target range, because double-click always selects a single cell.

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

  3. Instead of changing the value of the clue, this process is concerned with the changing the colour of the font.

Execute the following tests:

  1. Double-click clues inside and outside RowClues.

  2. Double-click clues inside and outside ColClues,

  3. Double-click the same clue twice or more.

 

Further Assignments

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:

  1. How does the flickering (caused by selection of Home) diminish from the experience?

  2. 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?

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

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

  1. Can you create a new module containing the bulk of the code, written once, and minimise the code in each worksheet that gets replicated?

 


461 views0 comments

Related Posts

See All
bottom of page