Check/Uncheck Box with Spacebar Keystroke Not Working

Question

Hi Excel Community,

I have created a worksheet with checkboxes that are placed in cells. When I tab to the cell with the checkbox in it and press the space bar shortcut, nothing changes the state of the checkbox.  The only thing that can change the state is a mouse click.  

Perhaps I missed a step to assign the checkbox to the cell it has been placed upon?  

Perhaps another keystroke is required to select the checkbox within the cell?  

Thank you kindly for any ideas!

Answer 1

I don't think there is any direct way to change the state of a checkbox on a worksheet by using the space bar.  In a Userform you can use the space bar because the focus is in the control; on a worksheet the focus is in the active cell.

The only way I can think of is to have code that intercepts the use of a space key and uses it to change the state of a checkbox whose top left corner is in the active cell:

Sub SpaceOn()

  Application.OnKey " ", "SpacePress"

End Sub

Sub SpacePress()

  Dim CB As CheckBox

  For Each CB In ActiveSheet.CheckBoxes

    If CB.TopLeftCell.Address = ActiveCell.Address Then If CB.Value = xlOn Then CB.Value = xlOff Else CB.Value = xlOn

  Next

End Sub

Sub SpaceOff()

  Application.OnKey " "

End Sub

You start the unusual behaviour by running SpaceOn and stop it by running SpaceOff.  The tricky bit is that you will want to use the spacebar normally in other contexts so you probably want to SpaceOn when the active cell moves into a particular range of cells and off when it moves out, or a different worksheet or workbook is activated or the workbook is closed.  Quite hard to get all the conditions right.

If you want help down that route please come back.

I should have said - the above code assumes the checkboxes are from the Forms toolbar - different code would be needed for the ActiveX type of checkbox from the Control Toolbox.