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

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

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

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

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

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