Software

Ask something
Discussion Locked

Data Entry Macro

0 helpful
7 replies

by audit

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How do you get a macro in VBA to pause for data entry? I tried to record a macro that moves from the first cell requiring data through the last cell that requires data. However it doesn't interpet my input as a pause for data entry {?}. Instead, when the macro is run, the cursor moves from the first cell to the next until it reaches the last cell and the macro terminates.

7 replies

by audit

This question was incorrectly closed. I am still in need of assistance.

by dklippert

Here's the macro I posted earlier. You can change the rows chosen by editing the numbers in the array. The secret is While-Wend and incrementing the MyCol variable.
Please comment and/or include the code you're using now for a more precise answer.
Here's a macro I picked up somewhere:

Sub EnterRows136()
Dim i As Variant
Dim myCol As Long
Dim myArray As Variant
Dim myResponse As Variant
myArray = Array(1, 3, 6)

myResponse = vbYes
myCol = ActiveCell.Column
While myResponse = vbYes
For Each i In myArray
Cells(i, myCol).Value = _
Application.InputBox("Enter value for Row " & i & " of Column " & _
Mid(Cells(i, myCol).Address, 2, InStr(2, Cells(i, myCol).Address, "$") - 2))
Next i
myCol = myCol + 1
myResponse = MsgBox("Continue?", vbYesNo)
Wend
End Sub

by shanghai sam

Here is the code that I created using the "Macro Record" feature of Excel. Where the double quotes appear is where the values that I entered when recording appeared. I need for macro to pause for user input at those points.

' Keyboard Shortcut: Ctrl+a
'
Application.Goto Reference:="VAL_PER_TRAN"
Application.Goto Reference:="DATA1"
ActiveCell.Formula = "1"
Application.Goto Reference:="DATA2"
ActiveCell.FormulaR1C1 = "2"
Application.Goto Reference:="DATA3"
ActiveCell.FormulaR1C1 = "2"
Application.Goto Reference:="DATA4"
ActiveCell.FormulaR1C1 = "2"
Application.Goto Reference:="DATA5"
ActiveCell.FormulaR1C1 = "4"
Application.Goto Reference:="DATA6"
ActiveCell.FormulaR1C1 = "3"
Application.Goto Reference:="DATA7"
ActiveCell.FormulaR1C1 = "1"
Application.Goto Reference:="DATA8"
ActiveCell.FormulaR1C1 = "1"
Application.Goto Reference:="DATA9"
ActiveCell.FormulaR1C1 = "2"
Application.Goto Reference:="DATA10"
Application.Goto Reference:="Print_Area"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="VAL_PER_TRAN"
Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Application.Goto Reference:="R1C1"
End Sub

by dklippert

This article should put you in the right direction.

(Type the Q number in the search box at support.microsoft.com)

How to Use a UserForm for Entering Data

Q213749

by dklippert

Here's a simple macro that you can stick in your code. A UserForm would be less annoying.

Sub DataEntry()
For X = 1 to 10
Data = InputBox("What's the entry for Data" & X & "?")
Range("Data" & X).Value = Data
Next X
End Sub

by audit

Thank you.
The Q213749 article was most enlighting.
You are right, a Userform would be less annoying.
I will have to take the time to develop.

This discussion is closed to new replies.