PDA

View Full Version : Use KB shortcut keys to return a value from a range



swaggerbox
10-04-2016, 05:21 AM
In Sheet2, I have listed, for example, the following values (A1-A9):

CTRL-1: The
CTRL-2: Quick
CTRL-3: Brown
CTRL-4: Fox
CTRL-5: Jumps
CTRL-6: Over
CTRL-7: The
CTRL-8: Lazy
CTRL-9: Dog

How do I paste the value, for example, "The" in Sheet1 upon pressing CTRL-1 (or their assigned keyboard shortcut as defined in Sheet2? You can suggest an alternative way of doing this. Basically, a user can just change the values and the assigned KB shortcuts as he/she deems fit.

offthelip
10-04-2016, 10:49 AM
try this: run the initalised macro to setup the short cut keys
Sub initialise()
For i = 1 To 9
With Application
.OnKey Key:="^" & i, Procedure:="Macro" & i
End With
Next i


End Sub


Sub macro1()
ActiveCell.Value = Worksheets("sheet2").Cells(1, 1).Value
End Sub
Sub macro2()
ActiveCell.Value = Worksheets("sheet2").Cells(2, 1).Value
End Sub
Sub macro3()
ActiveCell.Value = Worksheets("sheet2").Cells(3, 1).Value
End Sub
Sub macro4()
ActiveCell.Value = Worksheets("sheet2").Cells(4, 1).Value
End Sub
Sub macro5()
ActiveCell.Value = Worksheets("sheet2").Cells(5, 1).Value
End Sub
Sub macro6()
ActiveCell.Value = Worksheets("sheet2").Cells(6, 1).Value
End Sub
Sub macro7()
ActiveCell.Value = Worksheets("sheet2").Cells(7, 1).Value
End Sub
Sub macro8()
ActiveCell.Value = Worksheets("sheet2").Cells(8, 1).Value
End Sub
Sub macro9()
ActiveCell.Value = Worksheets("sheet2").Cells(9, 1).Value
End Sub

swaggerbox
10-04-2016, 11:43 PM
Great! Thanks a lot!