+ Reply to Thread
Results 1 to 7 of 7

Select range with InputBox. Is it possible?

  1. #1
    Isabel
    Guest

    Select range with InputBox. Is it possible?

    Hello!

    I posted yesterday because I needed help to select ranges to make a chart
    with a macro, and I have another question related with this.
    I thought I always knew in what columns my x and y values where but it may
    not be true.
    What I need is to ask the user with an input box to tell me what is the
    first cell of data to my x and y values, and then use these values to select
    the ranges for the graph, instead of selecting always the same ranges.

    x_cell = InputBox("Insert the first cell of data for x values, e.g A8")
    y_cell = InputBox("Insert the first cell of data for y values, e.g B8")

    Range("A10").Select
    Set x_values = Range("A10", Range("A10").End(xlDown))
    Set y_values = x_values.Offset(0, 1)

    Is it possible? How?
    Thank you very much for your help.

    Best regards,

    Isabel

  2. #2
    Tom Ogilvy
    Guest

    RE: Select range with InputBox. Is it possible?

    Use the Excel inputbox (application.Inputbox) instead of the VBA inputbox
    (Inputbox). In you module type application.Inputbox, highlight it and hit
    F1 to get to the help on it.

    On Error Resume Next
    Set x_cell = Application.InputBox( _
    "Insert the first cell of data for x values, e.g A8",type:=8)
    Set y_cell = Application.InputBox( _
    "Insert the first cell of data for y values, e.g B8",type:=8)
    On Error goto 0
    if x_cell is nothing or y_cell is nothing then
    Msgbox "You hit cancel, exiting"
    exit sub
    end if
    'in case user selected more than one cell
    set x_cell = x_cell(1)
    set y_cell = y_cell(1)

    ' not define your ranges.

    --
    Regards,
    Tom Ogilvy


    "Isabel" wrote:

    > Hello!
    >
    > I posted yesterday because I needed help to select ranges to make a chart
    > with a macro, and I have another question related with this.
    > I thought I always knew in what columns my x and y values where but it may
    > not be true.
    > What I need is to ask the user with an input box to tell me what is the
    > first cell of data to my x and y values, and then use these values to select
    > the ranges for the graph, instead of selecting always the same ranges.
    >
    > x_cell = InputBox("Insert the first cell of data for x values, e.g A8")
    > y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    >
    > Range("A10").Select
    > Set x_values = Range("A10", Range("A10").End(xlDown))
    > Set y_values = x_values.Offset(0, 1)
    >
    > Is it possible? How?
    > Thank you very much for your help.
    >
    > Best regards,
    >
    > Isabel


  3. #3
    Tom Ogilvy
    Guest

    RE: Select range with InputBox. Is it possible?

    Use the Excel inputbox (application.Inputbox) instead of the VBA inputbox
    (Inputbox). In you module type application.Inputbox, highlight it and hit
    F1 to get to the help on it.

    On Error Resume Next
    Set x_cell = Application.InputBox( _
    "Insert the first cell of data for x values, e.g A8",type:=8)
    Set y_cell = Application.InputBox( _
    "Insert the first cell of data for y values, e.g B8",type:=8)
    On Error goto 0
    if x_cell is nothing or y_cell is nothing then
    Msgbox "You hit cancel, exiting"
    exit sub
    end if
    'in case user selected more than one cell
    set x_cell = x_cell(1)
    set y_cell = y_cell(1)

    ' not define your ranges.

    --
    Regards,
    Tom Ogilvy


    "Isabel" wrote:

    > Hello!
    >
    > I posted yesterday because I needed help to select ranges to make a chart
    > with a macro, and I have another question related with this.
    > I thought I always knew in what columns my x and y values where but it may
    > not be true.
    > What I need is to ask the user with an input box to tell me what is the
    > first cell of data to my x and y values, and then use these values to select
    > the ranges for the graph, instead of selecting always the same ranges.
    >
    > x_cell = InputBox("Insert the first cell of data for x values, e.g A8")
    > y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    >
    > Range("A10").Select
    > Set x_values = Range("A10", Range("A10").End(xlDown))
    > Set y_values = x_values.Offset(0, 1)
    >
    > Is it possible? How?
    > Thank you very much for your help.
    >
    > Best regards,
    >
    > Isabel


  4. #4
    Isabel
    Guest

    RE: Select range with InputBox. Is it possible?

    I tried and it returns me the value inside the cell I select, not a reference
    to a cell...
    What I needed is to do something like this, wether it is with
    Application.InputBox or not:

    y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    Range(y_cell).Select

    Is it possible? Can it be done in another way?

    Thanks




    "Tom Ogilvy" wrote:

    > Use the Excel inputbox (application.Inputbox) instead of the VBA inputbox
    > (Inputbox). In you module type application.Inputbox, highlight it and hit
    > F1 to get to the help on it.
    >
    > On Error Resume Next
    > Set x_cell = Application.InputBox( _
    > "Insert the first cell of data for x values, e.g A8",type:=8)
    > Set y_cell = Application.InputBox( _
    > "Insert the first cell of data for y values, e.g B8",type:=8)
    > On Error goto 0
    > if x_cell is nothing or y_cell is nothing then
    > Msgbox "You hit cancel, exiting"
    > exit sub
    > end if
    > 'in case user selected more than one cell
    > set x_cell = x_cell(1)
    > set y_cell = y_cell(1)
    >
    > ' not define your ranges.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Isabel" wrote:
    >
    > > Hello!
    > >
    > > I posted yesterday because I needed help to select ranges to make a chart
    > > with a macro, and I have another question related with this.
    > > I thought I always knew in what columns my x and y values where but it may
    > > not be true.
    > > What I need is to ask the user with an input box to tell me what is the
    > > first cell of data to my x and y values, and then use these values to select
    > > the ranges for the graph, instead of selecting always the same ranges.
    > >
    > > x_cell = InputBox("Insert the first cell of data for x values, e.g A8")
    > > y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    > >
    > > Range("A10").Select
    > > Set x_values = Range("A10", Range("A10").End(xlDown))
    > > Set y_values = x_values.Offset(0, 1)
    > >
    > > Is it possible? How?
    > > Thank you very much for your help.
    > >
    > > Best regards,
    > >
    > > Isabel


  5. #5
    Tom Ogilvy
    Guest

    RE: Select range with InputBox. Is it possible?

    No, it doesn't return the value of the cell selected. try this

    Sub HelpMeLearn()
    Dim rng as Range
    On Error Resume Next
    set rng = Application.InputBox("Select cell(s) with mouse",type:=8)
    On error go to 0
    if not rng is nothing then
    rng.select
    msgbox "Range selected is " & rng.Address
    else
    msgbox "You hit cancel"
    End if
    End sub

    --
    Regards,
    Tom Ogilvy


    "Isabel" wrote:

    > I tried and it returns me the value inside the cell I select, not a reference
    > to a cell...
    > What I needed is to do something like this, wether it is with
    > Application.InputBox or not:
    >
    > y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    > Range(y_cell).Select
    >
    > Is it possible? Can it be done in another way?
    >
    > Thanks
    >
    >
    >
    >
    > "Tom Ogilvy" wrote:
    >
    > > Use the Excel inputbox (application.Inputbox) instead of the VBA inputbox
    > > (Inputbox). In you module type application.Inputbox, highlight it and hit
    > > F1 to get to the help on it.
    > >
    > > On Error Resume Next
    > > Set x_cell = Application.InputBox( _
    > > "Insert the first cell of data for x values, e.g A8",type:=8)
    > > Set y_cell = Application.InputBox( _
    > > "Insert the first cell of data for y values, e.g B8",type:=8)
    > > On Error goto 0
    > > if x_cell is nothing or y_cell is nothing then
    > > Msgbox "You hit cancel, exiting"
    > > exit sub
    > > end if
    > > 'in case user selected more than one cell
    > > set x_cell = x_cell(1)
    > > set y_cell = y_cell(1)
    > >
    > > ' not define your ranges.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "Isabel" wrote:
    > >
    > > > Hello!
    > > >
    > > > I posted yesterday because I needed help to select ranges to make a chart
    > > > with a macro, and I have another question related with this.
    > > > I thought I always knew in what columns my x and y values where but it may
    > > > not be true.
    > > > What I need is to ask the user with an input box to tell me what is the
    > > > first cell of data to my x and y values, and then use these values to select
    > > > the ranges for the graph, instead of selecting always the same ranges.
    > > >
    > > > x_cell = InputBox("Insert the first cell of data for x values, e.g A8")
    > > > y_cell = InputBox("Insert the first cell of data for y values, e.g B8")
    > > >
    > > > Range("A10").Select
    > > > Set x_values = Range("A10", Range("A10").End(xlDown))
    > > > Set y_values = x_values.Offset(0, 1)
    > > >
    > > > Is it possible? How?
    > > > Thank you very much for your help.
    > > >
    > > > Best regards,
    > > >
    > > > Isabel


  6. #6
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    selection of a row with two inputboxs

    Helo!

    I have a database in excel for devices for each device I have 11 columns! To modify a device I have created 2 inputbox that will ask the user to enter the value of 2 columns: The SN (serial number) and HT (manufacturer)
    If the values in each input box correpond to the same row in the database i want to select the entire row and put the selection in a listbox (I've done this job with only one inputbox with the following code):

    Please Login or Register  to view this content.


    I want to add another inputbox to ask the user to enter the manufacturer because sometimes two devices can have the same SN! so I wrote this but I don't know how to make it work!!

    Please Login or Register  to view this content.


    Anyone can help??
    Thanks!!

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Select range with InputBox. Is it possible?

    Sebastienkanj,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1