Page 1 of 1
Assign Macro to key event
Posted: Mon Nov 21, 2011 12:14 pm
by arrowman
Hello,
I would like to assign m\Macro to key "pressed event". Is this possible?
I wanna make macro which move selection to next column when user press "enter" in last row of actual column.
Thank you,
A.
Re: Assign Macro to key event
Posted: Tue Nov 22, 2011 12:00 am
by Charlie Young
I don't think you can directly assign a macro to the Enter key, but you can intercept it with a KeyHandler and make it run a macro. I had something close to this from an old post somewhere around here, so I'll present it even though it has a few quirks.
The attached spreadsheet has a button in H1 to start and stop the KeyHandler. The main action of the KeyHandler is in the KeyPressed function. Note first of all that setting KeyHandler_KeyPressed = True prevents the default action (such as go down) from happening. The data in B2:F16 are from the old post, and I don't remember what exactly they were for, but they serve the purpose here well enough, I guess.
Code: Select all
function KeyHandler_KeyPressed(oEvent) as boolean
Dim oCursor As Object
Dim oSelection As Object
if oEvent.KeyCode = 1280 then
oSelection = ThisComponent.CurrentController.Selection
oCursor = ThisComponent.CurrentController.ActiveSheet.createCursorByRange(oSelection)
oCursor.collapseToCurrentRegion()
if oCursor.RangeAddress.EndRow = oSelection.CellAddress.Row and oCursor.RangeAddress.EndRow <> oCursor.RangeAddress.StartRow then
KeyHandler_KeyPressed = True
GoRight
else
KeyHandler_KeyPressed = False
endif
else
KeyHandler_KeyPressed = False
endif
end function
The major quirks I alluded to are with the method of determining the end of data in the column. Here I am creating a SheetCellCursor and using
collapseToCurrentRegion(). This works pretty much, except that the CurrentRegion also includes the surrounding blank columns and/or rows if one of these is selected. There are other methods of dealing with the end of data, but I'll present this here for possible discussion.
Re: Assign Macro to key event
Posted: Sat Feb 17, 2018 1:43 am
by graybeard1ca
Thanks for this! I had a similar need -- to jump from the bottom of one column to the top of the next when entering data. Connecting my recorded and edited macro to Shift+Enter did the trick. Enter a column of data, press SHIFT+ENTER and I'm ready for the next column.
