Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Select Range off sheet from inputbox | Excel Discussion (Misc queries) | |||
InputBox to select cell locations | Excel Worksheet Functions | |||
Inputbox - Select ranges | Excel Programming | |||
Other option of InputBox for range selection | Excel Programming | |||
Trying to return Range from InputBox | Excel Programming |