Sunday, April 6, 2014

Simulated touch box in Excel for touch screen

I have a client that wants to build an Excel spreadsheet for use on a touch screen.  They would like to have about 15 columns of check boxes for about 600 rows. Reading articles about the check box controls for Excel, I was pretty sure I did not want to use them, for a few reasons:
  1. They're hard to create and associate with the cell if you need hundreds. If you copy and paste them, the underlying cell association isn't copied, so you'd have to manually click and set the cell for each one.  (Perhaps it could be programmed with VBA)
  2. Once you do get them all created and associated, deleting a row of the spread sheet doesn't delete the check box so it throws off all of the associations
  3. They are very small and can be hard to use and hard to see when printed
Instead, I started looking for a simulated solution.  What I really wanted was to handle the Click event for a cell and just set the text to X if it was empty, or empty if it was X.  Seemed simple enough.  First problem was that Excel does not provide a handler for the Click event.  The closest are BeforeDoubleClick and BeforeRightClick.  I wasn't sure how a right click would happen with a touch screen, but a double click sounded good enough.  So that's what I tried first.  I wrote my code and put it in the BeforeDoubleClick handler for the worksheet, and it worked great....for the mouse.

That brings up the second problem.  Touch events aren't handled the same as an actual mouse click.  Double tapping the screen did not create the same behavior as a double click, and it did not fire the BeforeDoubleClick event.  I needed to start looking at the other possible events to handle.  Eventually I tried SelectionChanged.  To select a single cell, you just click it once, or tap it once.  That's just what I needed.  I moved my code to that event and it worked great...for the mouse and touching!  I had to make one small change to ignore cases where multiple cells were selected, but that was trivial. The real catch is that you can't select a selected cell. So if you a click a cell to change its value, then you have to click somewhere else first before you can click/select that cell again.  We thought that was an acceptable catch since it would only be needed if the user accidentally clicked a wrong cell and needed to uncheck it.

Here's the final code for the event handler. The big If statement ensures that it isn't a multiple cell selection and ensures that its one of the cells that we want to use as a check box:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 And Target.Row >= 20 And Target.Column >= 27 And Target.Column <= 40 Then
        If Target.Value = "" Then
            Target.Value = "X"
            Target.Value = ""
        End If
    End If
End Sub


  1. Hi.
    I have almost exactly same problem, but your solution doesn't work... And I dont understand your makro :(
    So I cant modify it.

    My makro (after 2x mouse click) add word "OK" in special cell (it have to be light grey - first grey after white from color palet)
    But my makro doesnt work on tablem...

    could you explain me in which cells works your makro?
    Best regards Jakub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)

    If Target.Address = "$P$6" Or Target.Address = "$Q$6" Then GoTo czy_ok
    If Target.Interior.Color = RGB(242, 242, 242) Then
    If Target.Value = "OK" Then Target.Value = "" Else Target.Value = "OK"
    Cancel = True
    End If
    End Sub

  2. Hi Jakub,
    Instead of using Target.Address as you are in your solution, I used Target.Row and Target.Column. I'm only setting the cell value if Target.Row is >= 20, so that's ALL rows from row 20 and onward. And I'm checking that Target.Column is >=27 and <= 40, so that corresponds to columns "AA" to "AN". And additionally, I'm making sure that the target is just one cell, rather than a range of cells.

    Hope that helped!