input box
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: input box

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!



  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Guest

    Default

    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

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"
    "

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    ur really great. Thanx a lot

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com