excelvba

keyboard keys to click form control spin button


Is there a way to use a keyboard key, rather than clicking up and down on a Form Control Spin Button? I don't like having to move my hand from the keyboard to the mouse.

I'm assuming it will be a macro, and I'm assuming it will be a simple solution. But haven't gotten anything yet.


Solution

  • You for sure can! A few steps are required though:

    Link your Control Spin Button

    Make sure your Form Control Spin Button is already linked to a specific cell (A1, etc...). To do this, right click on the button and select 'Format Control'. Set your 'Current Value', 'Min', 'Max' and 'Cell Link' values.

    Create your macros

    Open VBA editor into a new module and paste the code below for your SpinUp and SpinDown controls.

    Sub SpinUp()
        With Range("A1")
            If .Value < 100 Then ' Max value
                .Value = .Value + 1
            End If
        End With
    End Sub
    
    Sub SpinDown()
        With Range("A1")
            If .Value > 0 Then ' Min value
                .Value = .Value - 1
            End If
        End With
    End Sub
    

    Assign your keyboard shortcuts

    Go to the Developer tab or Alt + F8 then click 'Options' under both the SpinUp and SpinDown macros and set your shortcuts. I believe Excel only supports Ctrl + <Letter> for macro shortcuts (not arrow keys or numbers), so maybe use Ctrl + U and Ctrl + D or any unused shortcut letters like Ctrl + J or Ctrl + M as 'U' and 'D' might replace some of Excell's built in shortcuts.

    From the help in the comments below: You may actually be able to use 'Shift' in combination with 'Ctrl + <Letter>' ( Ctrl + Shift + U and Ctrl + Shift + D )