input box

Joanna_gr

Board Regular
Joined
Feb 16, 2002
Messages
149
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! :)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"
"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top