pausing a macro to input cell contents
 

My first macro using XP, Excel 2003.

During the Recording of a macro, what syntax/keystroke is required to
'pause' a macro so as to allow input of data into a cell address, and then
re-initate continuance of the macro to its next step? I do not want to end
or stop the macro, just pause it automatically to enter data.

pausing a macro to input cell contents
 

Pause for input........................

Sub getuserinput()
'some of your code goes here
usrinput = InputBox("enter a number")
Range("A1").Value = usrinput
'resume rest of code
End Sub

Or if you want to select a cell or range to operate on...........

Sub selectit()
'your code
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
'do something with srng
End Sub

Combination of the two above into one...............

Sub selectit()
usrinput = InputBox("enter a string")
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
For Each cell In srng
cell.Value = usrinput
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 12 Jan 2009 16:38:06 -0800, blipityblap
wrote:

My first macro using XP, Excel 2003.

During the Recording of a macro, what syntax/keystroke is required to
'pause' a macro so as to allow input of data into a cell address, and then
re-initate continuance of the macro to its next step? I do not want to end
or stop the macro, just pause it automatically to enter data.

pausing a macro to input cell contents
 

Thank you very much. I now realize I must read a book on Visual Basic in
order to construct an auto-record Excel macro. Likely I'll simply go back to
Quattro Pro which has been working well for 18 years.

"Gord Dibben" wrote:

Pause for input........................

Sub getuserinput()
'some of your code goes here
usrinput = InputBox("enter a number")
Range("A1").Value = usrinput
'resume rest of code
End Sub

Or if you want to select a cell or range to operate on...........

Sub selectit()
'your code
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
'do something with srng
End Sub

Combination of the two above into one...............

Sub selectit()
usrinput = InputBox("enter a string")
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
For Each cell In srng
cell.Value = usrinput
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 12 Jan 2009 16:38:06 -0800, blipityblap
wrote:

My first macro using XP, Excel 2003.

During the Recording of a macro, what syntax/keystroke is required to
'pause' a macro so as to allow input of data into a cell address, and then
re-initate continuance of the macro to its next step? I do not want to end
or stop the macro, just pause it automatically to enter data.

pausing a macro to input cell contents
 

Yes, unfortunately you cannot record a macro and get things like InputBoxes.

You have to read a book or look to the VBA Help section of Excel


Gord

On Tue, 13 Jan 2009 10:26:01 -0800, blipityblap
wrote:

Thank you very much. I now realize I must read a book on Visual Basic in
order to construct an auto-record Excel macro. Likely I'll simply go back to
Quattro Pro which has been working well for 18 years.

"Gord Dibben" wrote:

Pause for input........................

Sub getuserinput()
'some of your code goes here
usrinput = InputBox("enter a number")
Range("A1").Value = usrinput
'resume rest of code
End Sub

Or if you want to select a cell or range to operate on...........

Sub selectit()
'your code
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
'do something with srng
End Sub

Combination of the two above into one...............

Sub selectit()
usrinput = InputBox("enter a string")
Set srng = Application.InputBox(prompt:= _
"Select a Range of cells", Type:=8)
For Each cell In srng
cell.Value = usrinput
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 12 Jan 2009 16:38:06 -0800, blipityblap
wrote:

My first macro using XP, Excel 2003.

During the Recording of a macro, what syntax/keystroke is required to
'pause' a macro so as to allow input of data into a cell address, and then
re-initate continuance of the macro to its next step? I do not want to end
or stop the macro, just pause it automatically to enter data.