General discussion

Locked

Data Entry Macro

By audit ·
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.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Data Entry Macro

by audit In reply to Data Entry Macro

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

Collapse -

Data Entry Macro

by DKlippert In reply to Data Entry Macro

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

Collapse -

Data Entry Macro

by Shanghai Sam In reply to Data Entry Macro

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

Collapse -

Data Entry Macro

by DKlippert In reply to Data Entry Macro

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

Collapse -

Data Entry Macro

by DKlippert In reply to Data Entry Macro

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

Collapse -

Data Entry Macro

by audit In reply to Data Entry Macro

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.

Collapse -

Data Entry Macro

by audit In reply to Data Entry Macro

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums