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

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

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

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