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

  • Thread starter Thread starter Wu
  • Start date Start date
W

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

NelsonTan said:
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 said:
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.
Click to expand...
Click to expand...
 
As per "Wu" requirement, only when the button "Enter" then macro start to
work. Therefore, the "~" is represent a [Enter] button.

A possible solution said:
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

NelsonTan said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top