Excel VB macro

March 12, 2009 at 09:50:05
Specs: Windows XP
How do I pause (or halt) an Excel macro so that I can input data in one cell, press the ENTER key and make the macro move on?

See More: Excel VB macro

Report •


#1
March 12, 2009 at 10:20:27
How about this:

Sub GetInput()
'Code to do something:
  For MyNum = 1 To 100
   DoSomething = MyNum
  Next
'Code to pause and get input
  GetNum = Application.InputBox("Please Enter The Number For A1", "Get Number", Type:=1)
'Continue - put number in cell and then use number from cell
  Range("A1") = GetNum
  MsgBox ("You Put " & Range("A1") & " In A1")
End Sub


Report •

#2
March 12, 2009 at 13:32:10
I'm impressed. That worked perfectly. Thanks huge! Since you're such a genius, can you add a line for me? If I look at the Msg response for the input and decide I chose the wrong number, can you bail me out without finishing the macro that called this routine? I'm using a sub as the "caller" for the Input routine. When the input is satisfied and is finished, the caller continues with other action which would be undesireable if I happened to use an input number I didn't like.

Report •

#3
March 12, 2009 at 13:49:45
The MsgBox is not needed, it's just there to give the code something to do in my example.

If you want to verify the entry, there are multiple ways to do it, but we'd need to know what you are looking for. A simple way would be to check the number before it goes in the cell:

Sub GetInput()
'Code to do something:
  For MyNum = 1 To 100
   DoSomething = MyNum
  Next
'Code to pause and get input
GetInput:
  GetNum = Application.InputBox("Please Enter The Number For A1", "Get Number", Type:=1)
'Check response
   MyAns = MsgBox("Do you want " & GetNum & " In A1?", vbYesNo)
'If OK put value in cell
   If MyAns = vbYes Then
    Range("A1") = GetNum
    GoTo Done
   End If
'If not, try again?
   If MyAns = vbNo Then TryAgain = MsgBox("Do you want to enter a different number?", vbYesNo)
'If yes get new number
   If TryAgain = vbYes Then GoTo GetInput
'If not, exit
Done:
End Sub


Report •

Related Solutions

#4
March 16, 2009 at 13:55:16
Sorry I haven't responded to your last msg. I've been away. Yes, indeed. That worked also. I only had to tune it just a little to suit my on screen wording but and excellant solution. Again, thank you very much. Not likely, but if I can ever be of help to you, let me know.

Report •


Ask Question