Stopping a Macro

[Guest]

I read in an old Microsoft Excel "Function Reference Book" that it is
possible to stop a running Macro (at a particular cells or cells), wait until
a value is entered and have the macro continue. (after ENTER ?)

I tried something with the PAUSE / RESUME commands, but it does not seem to
work

 

[Guest]

You could try InputBox function to get user input and populate the required
cell from the input.

Regards,

OssieMac

 

[Guest]

Thank you,

will this stop the Macro and resume after entry?

 

[Bob Phillips]

The macro will wait until input is complete, and then will pick up after
that input (or Cancel).

You need to code for the result. Look it up in help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I tried the folling statement:

=INPUT("Enter Data",1)

but there is an error: "Expects #"


I want simply go to Cell A6, wait for an entry and resume with the Macro.

 

[Bob Phillips]

You are in the programming group, so it is InputBox, and it is VBA

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you!

the following

Sub EnterData()
'
' EnterData Macro
' Macro grabada el 14.08.2007 por fak
'
'
Application.Goto Reference:="R1C1"
InputBox (Enter)

Application.Goto Reference:="R5C8"

End Sub


works fine, Macro goes to A1, displays Box (without "Enter"...), but when I
fill in a value in the box, it is not accepted in cell A1. The macro then
continues going to A5.

It also asks for a "prompt" and default.

 

[aidan.heritage]

Thank you!

the following

Sub EnterData()
'
' EnterData Macro
' Macro grabada el 14.08.2007 por fak
'
'
Application.Goto Reference:="R1C1"
InputBox (Enter)

Application.Goto Reference:="R5C8"

End Sub

works fine, Macro goes to A1, displays Box (without "Enter"...), but when I
fill in a value in the box, it is not accepted in cell A1. The macro then
continues going to A5.

It also asks for a "prompt" and default.

--
Felix





- Show quoted text -

try

range("A1").value=inputbox("enter")

 

[Guest]

Thank you, I tried the following:


Sub EnterData()
'
' EnterData Macro
'
'
Application.Goto Reference:="R1C1"
InputBox (Enter)
Range("A1").Value = InputBox("enter")
Application.Goto Reference:="R8C5"
End Sub

Result: The value entered in the box (a number) is not in A1, however, the
word "enter" is now written onto the box.

I would need: The text "Please enter value" on the box and a numeric value
be put is cell A1.

 

[Guest]

Hi Felix,

Sorry I did not get back to you sooner. It looks like you need a little more
help than just a pointer in the right direction.

The following is an example of using InputBox with handling user cancelling
instead of entering data. Note that clicking OK on InputBox without entering
data is the same as clicking cancel.

Sub Input_Data()
Dim exitInput As Boolean
Dim msgResponse

exitInput = False

Range("A1").Select

Do While exitInput = False

Range("A1").Value = InputBox("Enter data for cell A1")

If Range("A1") = "" Then
msgResponse = MsgBox("You cancelled without entering data" _
& Chr(13) & "Click Yes if you meant to cancel or" _
& Chr(13) & "No if you still want to enter data", vbYesNo)

If msgResponse = vbNo Then
exitInput = False
Else
exitInput = True
End If
Else
exitInput = True
End If
Loop

End Sub

Regards,

OssieMac

 

[Guest]

Hi again Felix,

Just a little extra for you. I'll point out the problems with the macro you
posted.

Application.Goto Reference:="R1C1"

InputBox (Enter) 'This line is NOT correct and not required. (Delete it.)

'In the following line A1 is assigned the value that you enter in the
InputBox.
Range("A1").Value = InputBox("Please enter value")
Application.Goto Reference:="R8C5"

Note: You are entering the value in the input box and not trying
to enter a value directly in the cell A1 aren't you because you cannot
enter it directly in the cell; it must be in the input box and the code
then assigns the value to the cell.

The message between the double quotes in the InputBox function can be
any message you like. I have edited yours to what you requested.

You could use the above code if you do not want any validation of whether
the user actually enters data.

Regards,

OssieMac