About Us

TOMD

Thanks Steve, I'll give that a try.
--
TomD

"STEVE BELL" wrote:

Just thought of an easier way - use a change event in the worksheet module.
The code below counts the number of entries in the row of a selected cell.
If there are no entries, it selects the first cell in column A of the row.
All the user has to do is move to any cell in the next row. This can be
done
with the down arrow, or Enter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If WorksheetFunction.CountA(Rows(Target.Row)) = 0 Then
Cells(Target.Row, 1).Select
End If
Application.EnableEvents = True
End Sub

You can automate the column selection and first cell selection with the
below code.
This one responds each time a cell is changed. If the changed cell is in
columns A, B, C, or D
it will move to the next column. If the changed cell is in column E - it
will move to the first cell in the next row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 5 Then
Target.Offset(0, 1).Select
Else: Cells(Target.Row + 1, 1).Select
End If
End Sub
--
steveB

Remove "AYN" from email to respond
"TomD" wrote in message
...
I would like to create a macro that would improve efficiency in data input
to
an Excel spreadsheet. My operator needs to enter five columns of data per
row, then go to the next row's column A and start over. I've created a
simple
do loop to control cursor movement, but don't know how to pause the macro
while the operator keys in data. Is there any way to do this in VBA?

Here's what I have so far:

Sub detailline()
Dim c As Integer

Do Until c = 5
ActiveCell.Offset(0, 1).Select
c = c + 1
' I need to pause the macro at each column to allow for user input

Loop

ActiveCell.Offset(1, -5).Select
c = 0

End Sub

Thanks for your help,

--
TomD

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
pausing a macro to input cell contents	blipityblap	New Users to Excel	3	January 13th 09 11:01 PM
Spreadsheet pausing,calculating cells,up2 100%,each input,why?	Matticace	Excel Discussion (Misc queries)	1	August 23rd 05 01:33 PM
macro that promt user for input	Fercho	Excel Programming	1	March 16th 05 04:17 AM
Pausing a macro for user input	Wayne[_5_]	Excel Programming	1	October 24th 03 10:34 PM
Can a macro be suspended during user input?	David DeArmond	Excel Programming	2	August 1st 03 05:02 AM

Menu

About Us