How can I get the cursor to move right upon inserting a digit from 1 to 9 in a cell using VBA without having to press enter or return

How can I get the cursor to move right upon inserting a digit from
1 to 9 in a cell using VBA without having to press enter or return

VBA will not executive while in EditMode on a worksheet. You have to
manually exit EditMode to fire an event. This requires a keypress
(Enter, Tab, right arrow, Esc) IOW, I'm pretty sure what you want to do
isn't possible.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel doesn't know you have entered something in a cell until you
indicate you are finished with that cell by hitting Enter or hit an
arrow key(if not in edit mode)

Use rightarrow to move right when ready to go.

Gord

On Thu, 14 Mar 2013 20:09:23 +0000, Michel Gauthier
wrote:


How can I get the cursor to move right upon inserting a digit from 1
to 9 in a cell using VBA without having to press enter or return

Do you want this behavior to occur for all cells or only certain cells?

Howard wrote:
On Saturday, March 16, 2013 11:59:37 AM UTC-7, Paga Mike wrote:
Michel Gauthier;1610331 Wrote:

How can I get the cursor to move right upon inserting a digit from 1

to 9 in a cell using VBA without having to press enter or return



Do you want this behavior to occur for all cells or only certain cells?









--

Paga Mike

Do you have a solution for what Michel is asking?

If yes, I'd be interested in the all cell and the certain cells solution.

Regards,
Howard


it can probably be done at API level by modifying WM_CHAR message. it
would not be perfect and I am not sure if it is worth implementing.

On Sunday, March 17, 2013 2:25:13 AM UTC-7, witek wrote:
Howard wrote:

On Saturday, March 16, 2013 11:59:37 AM UTC-7, Paga Mike wrote:

Michel Gauthier;1610331 Wrote:



How can I get the cursor to move right upon inserting a digit from 1



to 9 in a cell using VBA without having to press enter or return







Do you want this behavior to occur for all cells or only certain cells?



















--



Paga Mike



Do you have a solution for what Michel is asking?



If yes, I'd be interested in the all cell and the certain cells solution.



Regards,

Howard





it can probably be done at API level by modifying WM_CHAR message. it

would not be perfect and I am not sure if it is worth implementing.

That would be way too deep for me.<G

Thanks for the info.

Howard

Hi Howard:

This can be accomplished with a set of OnKey macros. The macros allow a single digit to be pressed and the value enter in a cell without the ENTER key.

Here are the macros:

Sub KeyMapper()
Application.OnKey "0", "Zero"
Application.OnKey "1", "One"
Application.OnKey "2", "Two"
Application.OnKey "3", "Three"
Application.OnKey "4", "Four"
Application.OnKey "5", "Five"
Application.OnKey "6", "Six"
Application.OnKey "7", "Seven"
Application.OnKey "8", "Eight"
Application.OnKey "9", "Nine"
End Sub

Sub KeyUnmapper()
Application.OnKey "0"
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
Application.OnKey "5"
Application.OnKey "6"
Application.OnKey "7"
Application.OnKey "8"
Application.OnKey "9"
End Sub

Sub Zero()
ActiveCell.Value = 0
ActiveCell.Offset(0, 1).Select
End Sub

Sub One()
ActiveCell.Value = 1
ActiveCell.Offset(0, 1).Select
End Sub

Sub Two()
ActiveCell.Value = 2
ActiveCell.Offset(0, 1).Select
End Sub

Sub Three()
ActiveCell.Value = 3
ActiveCell.Offset(0, 1).Select
End Sub

Sub Four()
ActiveCell.Value = 4
ActiveCell.Offset(0, 1).Select
End Sub

Sub Five()
ActiveCell.Value = 5
ActiveCell.Offset(0, 1).Select
End Sub

Sub Six()
ActiveCell.Value = 6
ActiveCell.Offset(0, 1).Select
End Sub

Sub Seven()
ActiveCell.Value = 7
ActiveCell.Offset(0, 1).Select
End Sub

Sub Eight()
ActiveCell.Value = 8
ActiveCell.Offset(0, 1).Select
End Sub

Sub Nine()
ActiveCell.Value = 9
ActiveCell.Offset(0, 1).Select
End Sub

KeyMapper establishes the behavior and KeyUnmapper removes the behavior.

That's really neat! It reminded me of an early project where I did
something similar, but needed a way to pass parameters to the
'MoveCursor' procedure. OnKey doesn't support passing parameters and so
I did a rework of your example based on the code in that early project
in the event params are needed...

Option Explicit

Sub MapNumKeys()
With Application
.OnKey "0", "Keypress0": .OnKey "1", "Keypress1"
.OnKey "2", "Keypress2": .OnKey "3", "Keypress3"
.OnKey "4", "Keypress4": .OnKey "5", "Keypress5"
.OnKey "6", "Keypress6": .OnKey "7", "Keypress7"
.OnKey "8", "Keypress8": .OnKey "9", "Keypress9"
End With 'Application
End Sub

Sub UnmapNumKeys()
With Application
.OnKey "0": .OnKey "1": .OnKey "2": .OnKey "3": .OnKey "4"
.OnKey "5": .OnKey "6": .OnKey "7": .OnKey "8": .OnKey "9"
End With 'Application
End Sub

Sub Keypress0()
Call MoveCursor(0)
End Sub

Sub Keypress1()
Call MoveCursor(1)
End Sub

Sub Keypress2()
Call MoveCursor(2)
End Sub

Sub Keypress3()
Call MoveCursor(3)
End Sub

Sub Keypress4()
Call MoveCursor(4)
End Sub

Sub Keypress5()
Call MoveCursor(5)
End Sub

Sub Keypress6()
Call MoveCursor(6)
End Sub

Sub Keypress7()
Call MoveCursor(7)
End Sub

Sub Keypress8()
Call MoveCursor(8)
End Sub

Sub Keypress9()
Call MoveCursor(9)
End Sub

Sub MoveCursor(Num&)
With ActiveCell
.Value = Num: .Offset(0, 1).Select
End With 'ActiveCell
End Sub

...where my project had more params to process and moved to a named
range via Application.GoTo! I also do something similar when DV choices
are made for showing 'pages' of a worksheet-based 'wizard' type of
thing. Or when a number option is selected I display further input rows
in context to the option choice and move the cursor to the 1st input
field. This uses the _Change event rather than OnKey, but it sure makes
auto-advancing through forms very user friendly.

Reading your post reminded me of the project. ..thanks for posting!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, March 17, 2013 3:26:08 PM UTC-7, GS wrote:
That's really neat! It reminded me of an early project where I did

something similar, but needed a way to pass parameters to the

'MoveCursor' procedure. OnKey doesn't support passing parameters and so

I did a rework of your example based on the code in that early project

in the event params are needed...



Option Explicit



Sub MapNumKeys()

With Application

.OnKey "0", "Keypress0": .OnKey "1", "Keypress1"

.OnKey "2", "Keypress2": .OnKey "3", "Keypress3"

.OnKey "4", "Keypress4": .OnKey "5", "Keypress5"

.OnKey "6", "Keypress6": .OnKey "7", "Keypress7"

.OnKey "8", "Keypress8": .OnKey "9", "Keypress9"

End With 'Application

End Sub



Sub UnmapNumKeys()

With Application

.OnKey "0": .OnKey "1": .OnKey "2": .OnKey "3": .OnKey "4"

.OnKey "5": .OnKey "6": .OnKey "7": .OnKey "8": .OnKey "9"

End With 'Application

End Sub



Sub Keypress0()

Call MoveCursor(0)

End Sub



Sub Keypress1()

Call MoveCursor(1)

End Sub



Sub Keypress2()

Call MoveCursor(2)

End Sub



Sub Keypress3()

Call MoveCursor(3)

End Sub



Sub Keypress4()

Call MoveCursor(4)

End Sub



Sub Keypress5()

Call MoveCursor(5)

End Sub



Sub Keypress6()

Call MoveCursor(6)

End Sub



Sub Keypress7()

Call MoveCursor(7)

End Sub



Sub Keypress8()

Call MoveCursor(8)

End Sub



Sub Keypress9()

Call MoveCursor(9)

End Sub



Sub MoveCursor(Num&)

With ActiveCell

.Value = Num: .Offset(0, 1).Select

End With 'ActiveCell

End Sub



..where my project had more params to process and moved to a named

range via Application.GoTo! I also do something similar when DV choices

are made for showing 'pages' of a worksheet-based 'wizard' type of

thing. Or when a number option is selected I display further input rows

in context to the option choice and move the cursor to the 1st input

field. This uses the _Change event rather than OnKey, but it sure makes

auto-advancing through forms very user friendly.



Reading your post reminded me of the project. ..thanks for posting!



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi Paga, Garry,

Is there something special I need to do to make either of your codes to work? I copied and pasted each in the vb editor but the worksheet reacted normal when I entered a single didgit into a cell, ie. typed 3 into A1 and the cursor sat there and blinked until I hit Enter or the Right Arrow.

I did type in a number a number and when it did nothing I went back to the news group to re-read your posts in case I missed a step or something, when I went back to the sheet it reacted like an Enter command and the cursor moved to the next cell.

Howard

For my code to work you have to setup the "hooks" for the keys (run
'MapNumKeys') in order for them to use the 'MoveCursor' procedure.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, March 17, 2013 4:47:18 PM UTC-7, GS wrote:
For my code to work you have to setup the "hooks" for the keys (run

'MapNumKeys') in order for them to use the 'MoveCursor' procedure.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Got it. Map sets the "hooks" and when I want a 'normal' sheet UnMap unsets the 'hooks'

That's pretty slick, I'm suprised I don't see more calls for something like this in the groups. If my 1k memory serves me, the patent answer is "can't be done!".

Off the top of my head looks like one could throw in some change event macros and be able to dance and frolic all about a worksheet and never hit Enter.

I'll have to play with that.

So does your code eliminate the error that Paga's produces saying cannot run Book1!Three (three being the number keyed)?

Howard

For my code to work you have to setup the "hooks" for the keys (run
'MapNumKeys') in order for them to use the 'MoveCursor' procedure.

Actually, the names of the 2 procs in my project were...

Sub ToggleKeyHooks(sKeys$, Optional Reset As Boolean = False)
and
Sub ToggleMenuHooks(sMenus$, Optional Reset As Boolean = False)

...but I changed this in my post to follow the context of Praga Mike's
post. Reason is that not too many people are familiar with the
terminology relating to "hooking" built-in menus/keypress actions and
diverting them to run custom procedures. That said, here's the complete
code...

Sub ToggleKeyHooks(sKeys$, Optional Reset As Boolean = False)
' Hooks keyboard actions to run custom code.
' Restores keyboard actions to normal when True is passed in.
Dim vKeys, vKey, n&
vKeys = Split(sKeys, ",")
With Application
For n = LBound(vKeys) To UBound(vKeys)
vKey = Split(vKeys(n), "|")
If Reset Then .OnKey vKey(0) Else .OnKey vKey(0), vKey(1)
Next 'n
End With 'Application
End Sub 'ToggleKeyHooks

Sub ToggleMenuHooks(sMenus$, Optional Reset As Boolean = False)
' Hooks built-in menus to run custom code
' Restores menus to normal when True is passed in.
Dim vMenus, vMenu, n&
vMenus = Split(sMenus, ",")
For n = LBound(vMenus) To UBound(vMenus)
vMenu = Split(vMenus(n), "|")
With Application.CommandBars(vMenu(0))
If Reset Then .Reset Else .OnAction = vMenu(1)
End With 'Application.CommandBars
Next 'n
End Sub 'ToggleMenuHooks

...which I've been using for quite some time. I store these in a module
nameD "mMenus" which I can import to any project and modify to suit.

The underlying mechanism is to store predefined params as delimited
strings...

Public sKEY_HOOKS$ = "0|Zero,1|One,2|Two,3|Three,4|Four," _
& "5|Five,6|Six,7|Seven,8|Eight,9|Nine"

Public sMENU_HOOKS$ = "Save|HookSave,SaveAs...|HookSaveAs"

..where items are stored as 'value pairs', and used as follows...

Sub Auto_Open()
' Contains startup code
Call InitGlobals: StoreExcelSettings: SetupUI
Call ToggleKeyHooks(sKEY_HOOKS): ToggleMenuHooks sMENU_HOOKS
CreateMenus
End Sub 'Auto_Open

Sub Auto_Close()
' Contains shutdown/cleanup code
Call RestoreExcelSettings: ToggleKeyHooks sKEY_HOOKS, True
Call ToggleMenuHooks(sMENU_HOOKS, True): DeleteMenus
End Sub 'Auto_Close

What makes this nice is that I can send any other params I need/want to
change in context to the current user activity. For example, if I want
to temporarily hook the built-in 'Print' menu (on-the-fly) then...

Call ToggleMenuHooks("Print...|HookPrint") '//hook the menuitem
'do special printing via normal .PrintOut method (and its params)
Call ToggleMenuHooks("Print...|", True) '//reset the menuitem

...as the procs are generic and so don't need to know what's going on
beyond the scope of the params they're passed.

I mostly use just use ToggleKeyHooks for disabling the usual keyboard
shortcuts to open the VBE window, and access any part of the UI I don't
want users to get to. (Most my stuff uses its own instance of Excel
that I automate via a VB6.exe frontloader. Thus, I customize the UI
however I want. The end result is an app that doesn't give much hint
its user is working with Excel<g)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

So does your code eliminate the error that Paga's produces saying
cannot run Book1!Three (three being the number keyed)?

Note that I use a different naming convention...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Public sKEY_HOOKS$ = "0|Zero,1|One,2|Two,3|Three,4|Four," _
& "5|Five,6|Six,7|Seven,8|Eight,9|Nine"

I didn't follow my naming convention in the above declaration for
reasons of brevity. The line would be written with the proc names I
listed previously, which is to append the number to the word
"Keypress"...

Public sKEY_HOOKS$ = "0|Keypress0,1|Keypress1..."

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, March 17, 2013 6:35:51 PM UTC-7, GS wrote:
Public sKEY_HOOKS$ = "0|Zero,1|One,2|Two,3|Three,4|Four," _

& "5|Five,6|Six,7|Seven,8|Eight,9|Nine"



I didn't follow my naming convention in the above declaration for

reasons of brevity. The line would be written with the proc names I

listed previously, which is to append the number to the word

"Keypress"...



Public sKEY_HOOKS$ = "0|Keypress0,1|Keypress1..."



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Thanks Garry.

Will study that, hope to retain some of it least.

You really do my local community a great service by keeping me off the streets while I try to digest this heady Excel programming.<G

Howard

You really do my local community a great service by keeping me off
the streets while I try to digest this heady Excel programming.<G

I could say the same for my community during these winter months, but
when it passes I'll be out and about as much as possible!<g I live on
the St. Lawrence River across the street from a landmark waterfront
park. I can't hardly wait for the better weather...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I also didn't look closely at the delimited string before posting a
sample of my 'ToggleMenuHooks' procedure...

Sub ToggleMenuHooks(sMenus$, Optional bReset As Boolean = False)
' Hooks built-in menus to run custom code
' Restores menus to normal when True is passed in.
Dim vMenus, vMenu, n&
vMenus = Split(sMenus, ",")
For n = LBound(vMenus) To UBound(vMenus)
vMenu = Split(vMenus(n), "|")
With Application.CommandBars(vMenu(0)).Controls(vMenu(1 ))
If bReset Then .Reset Else .OnAction = vMenu(2)
End With 'Application.CommandBars().Controls()
Next 'n
End Sub 'ToggleMenuHooks

...as per copy/paste via opening a network file that contains the code.
Note that the delimited string contains "cbarname|ctrlname|onaction".
So my examples for doing Save,SaveAs are actually...

Public gsSAVE_HOOKS$ = "File|&Save|HookSave" _
& ",File|Save &As...|HookSaveAs"

...as taken directly from a project that runs its own routines for these
menus. (and yes, the keyboard shortcuts are also toggled)

Note also that earlier I did not prepend the Reset param with its data
type prefix as shown here. I guess that's the downside of not having my
newsreader on my dev machine, and so was not able to copy/paste. What
surprises me is that trying to suggest a commandbar has an onaction
property should have 'clicked' a cue that I was wrongly adlibbing what
I glanced over on the other machine. My bad! (I'll have to stop doing
that<g) I got lazy and copied the 'ToggleKeyHooks' proc and tried to
edit it as though it was 'ToggleMenuHooks'!

I'm having a dufus day for sure today!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'm having a dufus day for sure today!

Today's a better day!

After giving the location of the menu/key hook procs, I moved them out
of mMenus and into mSetupUI because these have nothing to do with my
runtime menus. I used this module long before I created mSetupUI and so
just left that code sitting where it always has been. Another proc I
moved along with these two is 'ToggleCommandbars' because it deals with
built-in commandbars only. Now mMenus only contains the code that
pertains to my runtime menus/toolbars.

Looks like I got a jump on some 'spring cleaning'!<G

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard:

For my code to work, just run KeyMapper

Hi Howard:

My code should work after KeyMapper has been run.

Be aware that the behavior is limited to the standard numeric keys (4 under $) You would need different coding to handle the numeric keypad. See:

http://www.vbaexpress.com/forum/showthread.php?t=16510