Page 1 of 1

Assign Macro to key event

PostPosted: Mon Nov 21, 2011 12:14 pm
by arrowman
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,

Re: Assign Macro to key event

PostPosted: Mon Nov 21, 2011 5:19 pm
by MrProgrammer
Hi, and welcome to the forum.

arrowman wrote:I wanna make macro which move selection to next column when user press "enter" in last row of actual column.
Look in Tools > Customize > Keyboard. Those are the keys that you can assign macros to. If you don't find "Enter" there, you could perhaps achieve the desired result by implementing "cell listeners". You could search for those terms in this forum but I suspect that implementing your macro via cell listeners would be rather complicated. I have not used cell listeners myself and don't know if they get control on Enter or not.

Instead of using a macro, take a look at Tools > Options > > General > Press Enter to move selection and see if that is sufficient for your work.
 Edit: I now see that the OP wants to move the selection horizontally only at the last data row of a column so Press Enter to move selection won't help. 

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Assign Macro to key event

PostPosted: 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   Expand viewCollapse view

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)
      if oCursor.RangeAddress.EndRow = oSelection.CellAddress.Row and oCursor.RangeAddress.EndRow <> oCursor.RangeAddress.StartRow then
         KeyHandler_KeyPressed = True
         KeyHandler_KeyPressed = False
      KeyHandler_KeyPressed = False
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

PostPosted: 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. :D :D