input box

[Joanna_gr]

hi! once more i need ur help!

How can i create an input box that pops up when i click on a specific cell and ask for a number to enter and after the imput to get as result the sum of the number that already exists on the cell + the number i've entered?
for example: A1 = 2 (a pop up window asks me to enter a new number let's say 2) after my action i have the result : A1 = 4.

Thanx for being so helpful!

[Derek]

Hi Joanna
You could try something like this event macro:

Right click your sheet tab, left click View Code and paste in the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Target.Value = Target.Value + InputBox("please enter the number 5")
End If
End Sub

Whenever you select A1 the input box will pop up, so you will probably have to make your first entry via the input box. If you pop up the box in error, just add 0

Hope this helps
Derek


[ This Message was edited by: Derek on 2002-02-27 21:22 ]

To avoid errors, you may want to validate the input of the end user, like such:

Target.Value = Target.Value + InputBox(prompt:="please enter the number 5",type:=1)

The "type" definition forces your user to enter a number.

Cheers,

Nate

[NateO]

Didn't mean to post anon....

To avoid errors, you may want to validate the input of the end user, like such:

Target.Value = Target.Value + InputBox(prompt:="please enter the number 5",type:=1)

The "type" definition forces your user to enter a number. The "type:=1" definition forces your user to enter a number. There is a variety of data validation techniques, see the vbe help assistant....

You can set a "guess" result by adding a "default" to your procedure: I.e.,

Target.Value = Target.Value + InputBox(prompt:="please enter the number 5",type:=1,default:=(target.value))


Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-27 21:48 ]

[Joanna_gr]

Thanks Derek That was really helpful. But what am i supposed to do when i have to get a prompt in a range of cells i.e. A1:A20. Can u please help me with that too?

Nate thanx. I'm not sure if i'm doing something wrong but when u use "Target.Value = Target.Value + InputBox(prompt:="please enter the number 5",type:=1,default:=(target.value))" I get a compile error on Type:=1 that says: "Named argument not found"
"

[Ivan F Moala]

Try;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rTargetRg As Range

Set rTargetRg = [a1:a20]
Set rTargetRg = Application.Intersect(rTargetRg, Target)

If Not rTargetRg Is Nothing Then
Target.Value = Target.Value + Application.InputBox("please enter a number", Type:=1)
End If
End Sub


Ivan

[Joanna_gr]

ur really great. Thanx a lot