Pausing macro for user Excel input

[TomD]

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,

 

[Ed]

What about a UserForm that has five TextBoxes for input? Tab from box to
box; Enter would then load the data in the next available row's A-E and
clear the form for the next row's data. This can all be done using ranges,
so no need to actively move from cell to cell. Any other way might actually
add time to the input process, because the operator will have to tell the
macro that input is done -your idea has that done for each cell!

Ed

 

[STEVE BELL]

One way is to build an input routine into your code and automate the input.
Loops work well for this...

You can do this with InputBoxes, UserForms, data import, and ?????

 

[STEVE BELL]

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

 

[TomD]

Ed,

Thanks for the suggestion. What I'm creating is a journal entry spreadsheet
to be uploaded into our general ledger accounting system. The first column is
for department number and the second column is for account number. Instead of
using combo box controls I've used the Excel Data/Validation/List feature;
because the chart of accounts is so long I don't want the user to have to
scroll down a list using a combo box but do want to control the input so that
only valid values from my chart of accounts are accepted.

I'm not too familiar with creating forms. Can I use the Data/Validation
feature in a form text box or would I have to use a combo box?

Thanks,

 

[TomD]

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

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

 

[Tom Ogilvy]

If data entry is only allowed in the first 5 columns, then select those five
columns and do Format=>Cells=>Protection Tab. Uncheck the Locked checkbox.
Then protect the sheet and only allow selection of unlocked cells. (if you
have xl2000 or xl97 you will have to set this the option to select only
unlocked cells in the properties window for the sheet).

 

[Ed]

It looks like you've got some other, better solutions. But if you'd like to
learn some more when you have time, check out www.contextures.com and the
VBA Help files for ListBox and ComboBox. The upshot is you have to create
lists for your data validation to work, and yes, you can put any list into a
ListBox, ComboBox, Option Buttons or CheckBoxes. Having said that, I have
to admit I have not done hardly any of that, and you would be better off
getting someone else's input on how to make it work well.

Ed