Assign Macro to key event

[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.

[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.

[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.