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.
You for sure can! A few steps are required though:
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.
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
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
)