USE EXCEL MARCO LIKE OLD VERSION LOTUS-123 ---> {?}

[Wu]

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.

 

[NelsonTan]

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

 

[A possible solution]

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/library/aa195807(office.11).aspx

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

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.

 

[NelsonTan]

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

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/library/aa195807(office.11).aspx

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

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.