Popup Input Box

[Dolphinv4]

Hi,

I'd like to have a message box popup whenever the word INPUT appears in a
certain cell.

Cell A1 have a formula to lookup another cell. If that cell does not have a
value, cell A1 will show the word INPUT. Whenever A1 shows INPUT, i want a
message box to appear to request for user input.

I know how to create the form for the input and i know how to place the
input value into cell A1, but I just do not know how to get the message box
to popup.

Also, I need excel to automatically monitor row by row, ie, users will key
in row by row. So, if A1 shows INPUT, the message box will appear. then after
they key in the INPUT and go on to the next row, if A2 shows INPUT again,
another message box will appear.

Thanks.

Dolphin

 

[Gary''s Student]

Here is part of your answer:

We assume that cell A1 contains a formula and that the formula may return
the value:

INPUT

The following worksheet event macro will examine A1 every time the worksheet
is calculated; If the value in A1 becomes INPUT, the input box is displayed.

Private Sub Worksheet_Calculate()
s = "INPUT"
Set r = Range("A1")
If r.Value <> s Then Exit Sub
x = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Dolphinv4]

HI,

I tried to do the below but it doesn't work. Any idea?

Private Sub Worksheet_Calculate()

s = "INPUT"
Set r2 = Range("P2")
If r2.Value <> s Then Exit Sub
x2 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P2") = x2

Set r3 = Range("P3")
If r3.Value <> s Then Exit Sub
x3 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P3") = x3

Set r4 = Range("P4")
If r4.Value <> s Then Exit Sub
x4 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P4") = x4

End Sub

Thanks.

 

[Gary''s Student]

The first problem that I see is that we are trying to test three cells.

If the first cell does not have INPUT, the routine exits and the other cells
are never checked. The first step will be to change the tests from:

Set r2 = Range("P2")
If r2.Value <> s Then Exit Sub
x2 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P2") = x2


to:

Set r2 = Range("P2")
If r2.Value = s Then
x2 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P2") = x2
End If

The second thing I noticed is that a cell in question, say P2, is being
examined. For the code to work, P2 must contain a formula, not a value. If
P2 does contain a formula and the formula returns INPUT, the code will
destroy the formula and replace it with the user input.

Are you sure this is what you want??

 

[Dolphinv4]

Hi,

it works exactly what I want now! Thanks!

The first problem that I see is that we are trying to test three cells.

If the first cell does not have INPUT, the routine exits and the other cells
are never checked. The first step will be to change the tests from:

Set r2 = Range("P2")
If r2.Value <> s Then Exit Sub
x2 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P2") = x2


to:

Set r2 = Range("P2")
If r2.Value = s Then
x2 = Application.InputBox(Prompt:=" Supply Input ", Type:=2)
Range("P2") = x2
End If

The second thing I noticed is that a cell in question, say P2, is being
examined. For the code to work, P2 must contain a formula, not a value. If
P2 does contain a formula and the formula returns INPUT, the code will
destroy the formula and replace it with the user input.

Are you sure this is what you want??

 

[Dolphinv4]

Hi,

one last qn. What if I want the promt to show "Supply Input in RC", where RC
is the cell where the INPUT originally appeared?

Thanks!

 

[Gary''s Student]

You could use something like:

x2 = Application.InputBox(Prompt:=" Supply Input " & r2.Address, Type:=2)

where, of course, we adjust the r2.Address to meet the need.