In the marco of Lotus 123, {?} represents that let us input data in current
cell when the marco running, and then we type the button [enter], the marco
will continue to run.


Now, I would like to write a marco in Excel. After running the marco, I can
input data in current cell, and I type [enter] button, it move to right cell
to let me input other data, after three times it moves down and move to left
three cells to let me input data repeatedly.

Hi Wu,
Your request sound like a contact data entry and I assume your column name
like below:
Col A Col B Col C
Name Title Tel contact
1 John Manager 1234567
2
3

Please try below macro, take note of I have added a Keyboard Shortcut:
Ctrl+a to triger the onkey macro. Or you may add a button to link to "Start"
macro.

Sub Start()
' Keyboard Shortcut: Ctrl+a
Application.OnKey "~", "Data_Entry"
Cells(65536, 1).End(xlUp).Activate
ActiveCell.Offset(1, 0).Select
End Sub

Sub Data_Entry()
Cells(65536, 1).End(xlUp).Activate
If ActiveCell.Offset(0, 1) = "" Then
ActiveCell.Offset(0, 1).Select
ElseIf ActiveCell.Offset(0, 2) = "" Then
ActiveCell.Offset(0, 2).Select
Else:
ActiveCell.Offset(1, 0).Select
End If
End Sub

If this post helps click Yes

"Wu" wrote:

In the marco of Lotus 123, {?} represents that let us input data in current
cell when the marco running, and then we type the button [enter], the marco
will continue to run.


Now, I would like to write a marco in Excel. After running the marco, I can
input data in current cell, and I type [enter] button, it move to right cell
to let me input other data, after three times it moves down and move to left
three cells to let me input data repeatedly.

What's the reason for the '~' key in the line
Application.OnKey "~", "Data_Entry"?

Shouldn't it rather be:
Application.OnKey "^{a}", "Data_Entry"?

see the online reference for the OnKey event:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"NelsonTan" wrote:

Hi Wu,
Your request sound like a contact data entry and I assume your column name
like below:
Col A Col B Col C
Name Title Tel contact
1 John Manager 1234567
2
3

Please try below macro, take note of I have added a Keyboard Shortcut:
Ctrl+a to triger the onkey macro. Or you may add a button to link to "Start"
macro.

Sub Start()
' Keyboard Shortcut: Ctrl+a
Application.OnKey "~", "Data_Entry"
Cells(65536, 1).End(xlUp).Activate
ActiveCell.Offset(1, 0).Select
End Sub

Sub Data_Entry()
Cells(65536, 1).End(xlUp).Activate
If ActiveCell.Offset(0, 1) = "" Then
ActiveCell.Offset(0, 1).Select
ElseIf ActiveCell.Offset(0, 2) = "" Then
ActiveCell.Offset(0, 2).Select
Else:
ActiveCell.Offset(1, 0).Select
End If
End Sub

If this post helps click Yes

"Wu" wrote:

In the marco of Lotus 123, {?} represents that let us input data in current
cell when the marco running, and then we type the button [enter], the marco
will continue to run.


Now, I would like to write a marco in Excel. After running the marco, I can
input data in current cell, and I type [enter] button, it move to right cell
to let me input other data, after three times it moves down and move to left
three cells to let me input data repeatedly.

As per "Wu" requirement, only when the button "Enter" then macro start to
work. Therefore, the "~" is represent a [Enter] button.

"A possible solution" wrote:

What's the reason for the '~' key in the line
Application.OnKey "~", "Data_Entry"?

Shouldn't it rather be:
Application.OnKey "^{a}", "Data_Entry"?

see the online reference for the OnKey event:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"NelsonTan" wrote:

Hi Wu,
Your request sound like a contact data entry and I assume your column name
like below:
Col A Col B Col C
Name Title Tel contact
1 John Manager 1234567
2
3

Please try below macro, take note of I have added a Keyboard Shortcut:
Ctrl+a to triger the onkey macro. Or you may add a button to link to "Start"
macro.

Sub Start()
' Keyboard Shortcut: Ctrl+a
Application.OnKey "~", "Data_Entry"
Cells(65536, 1).End(xlUp).Activate
ActiveCell.Offset(1, 0).Select
End Sub

Sub Data_Entry()
Cells(65536, 1).End(xlUp).Activate
If ActiveCell.Offset(0, 1) = "" Then
ActiveCell.Offset(0, 1).Select
ElseIf ActiveCell.Offset(0, 2) = "" Then
ActiveCell.Offset(0, 2).Select
Else:
ActiveCell.Offset(1, 0).Select
End If
End Sub

If this post helps click Yes

"Wu" wrote:

In the marco of Lotus 123, {?} represents that let us input data in current
cell when the marco running, and then we type the button [enter], the marco
will continue to run.


Now, I would like to write a marco in Excel. After running the marco, I can
input data in current cell, and I type [enter] button, it move to right cell
to let me input other data, after three times it moves down and move to left
three cells to let me input data repeatedly.