use inputbox to choose cells for plots in excel

Hi all,

The below code would I like to change so I can use the inputbox arrays 'usr_choice_x' and 'usr_choice_y' (instead of the range "A68:B89" as it is now) for the plot. How do I do that

Grateful for ideas

usr_choice_x = Application.InputBox(Prompt:="Select x cells ", Type:=64)
usr_choice_y = Application.InputBox(Prompt:="Select y cells ", Type:=64)

Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("MDO_061013_LHS_HP").Range( _
"A68:B89"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"MDO_061013_LHS_HP"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Displacement [mm]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Force "
End With

Kind regards

\Jonas

Answer this question

use inputbox to choose cells for plots in excel

Thomas Epting

Hi Jon,

I think I have fixed the problem. I edit your code as shown below and so far it seems to do it. Do you see any great disadvantage with it

I would appreciate if you can help me editing it so the chart is placed as an object on the worksheet where the data was collected. And not as now in a new sheet.

    Dim myXData As Range
    Dim myYData As Range

    ' What ranges contain X & Y data for chart
    Set myXData = Application.InputBox( _
        prompt:="Select a range containing the X data.", _
        Title:="Select Chart Data", Type:=8)
    Set myYData = Application.InputBox( _
        prompt:="Select a range containing the Y data.", _
        Title:="Select Chart Data", Type:=8)

    Charts.Add
    With ActiveChart
        .ChartType = xlXYScatterSmoothNoMarkers
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = myXData
        .SeriesCollection(1).Values = myYData
        .Location Where:=xlLocationAsNewSheet
    End With

Once again thanks for your time.

Best regards

\Jonas
raq
This is a bit more streamlined:

    Dim myXData As Range
    Dim myYData As Range

    ' What ranges contain X & Y data for chart
    Set myXData = Application.InputBox( _
        prompt:="Select a range containing the X data.", _
        Title:="Select Chart Data", Type:=8)
    Set myYData = Application.InputBox( _
        prompt:="Select a range containing the Y data.", _
        Title:="Select Chart Data", Type:=8)

    ' Add chart and populate with selected range above
    Charts.Add
    With ActiveChart
      ' remove any existing series
      Do While .SeriesCollection.Count > 0
        .SeriesCollection(.SeriesCollection.Count).Delete
      Loop

      ' add working series to chart
      With .SeriesCollection.NewSeries
        .Values = myYData
        .XValues = myXData
      End With
      .ChartType = xlXYScatterSmoothNoMarkers

      ' etc.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Mr. SOAPitStop
I forgot the case in which the chart starts with no series. I've inserted a block that fixes this:

Dim myXData As Range
Dim myYData As Range

' What ranges contain X & Y data for chart
Set myXData = Application.InputBox( _
prompt:="Select a range containing the X data.", _
Title:="Select Chart Data", Type:=8)
Set myYData = Application.InputBox( _
prompt:="Select a range containing the Y data.", _
Title:="Select Chart Data", Type:=8)

' Add chart and populate with selected range above
Charts.Add
With ActiveChart
' make sure only one series in chart
Do While .SeriesCollection.Count > 1
.SeriesCollection(.SeriesCollection.Count).Delete
Loop

' make sure at least one series in chart
If .SeriesCollection = 0 Then
.SeriesCollection.NewSeries
End If

.ChartType = xlXYScatterSmoothNoMarkers
With .SeriesCollection(1)
.Values = myYData
.XValues = myXData
End With

' etc.

Let me know if this works.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
meconnell

Hi,

I think the InputBox(Type:=8) to get the Range object is more suitable for your application. Then use Range.Address to get the address in string, then put it to the chart.
Darren.Sim

If you have a contiguous range with X and all of the Y, do it this way:

Dim myDataRange As Range

' What range contains data for chart
Set myDataRange = Application.InputBox( _
prompt:="Select a range containing the chart data.", _
Title:="Select Chart Data", Type:=8)

' Add chart and populate with selected range above
Charts.Add
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=myDataRange, PlotBy:=xlColumns

' etc.

If you need to select X and Y separately, do it this way:

Dim myXData As Range
Dim myYData As Range

' What ranges contain X & Y data for chart
Set myXData = Application.InputBox( _
prompt:="Select a range containing the X data.", _
Title:="Select Chart Data", Type:=8)
Set myYData = Application.InputBox( _
prompt:="Select a range containing the Y data.", _
Title:="Select Chart Data", Type:=8)

' Add chart and populate with selected range above
Charts.Add
With ActiveChart
' make sure only one series in chart
Do While .SeriesCollection.Count > 1
.SeriesCollection(.SeriesCollection.Count).Delete
Loop

.ChartType = xlXYScatterSmoothNoMarkers
With .SeriesCollection(1)
.Values = myYData
.XValues = myXData
End With

' etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
WinFormsUser13232

Hi Jon,

A new error occured.

Run-time error '438'

Object doesn't support this proporty or method

And it points to: If .SeriesCollection = 0 Then

Any ideas

Regards

Jonas
Evan Mulawski
I have used similar code for the input box. But the problem I am having is trapping the event when the user presses CANCEL.

Set myXData = Application.InputBox( _
prompt:="Select a range containing the X data.", _
Title:="Select Chart Data", Type:=8)

It the user presses CANCEL your get "Run-time error '424: Object required."

How can I properly get the application to terminated when the CANCEL button is pressed.

Thanks@
dHan61

Thanks Jon!

Now it works just fine.

Best Regards

\Jonas
Lunarpc2
The only disadvantage is if the active cell is in the middle of some data when the chart is created. Excel may start the chart with some series already, and your code leaves it in and adds a new series.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```

Siteadm

My bad. That should be

If .SeriesCollection.Count = 0 Then

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

xlordt

Hi Jon,

Thanks for your help your first example works like a charm. But I have now after testing your second example with selection of X and Y separately find a bug which I can't fix.

The error message is

Runtime error 1004

Method 'SeriesCollection' of object '_Chart' failed

And it marks line: With .SeriesCollection(1)

It doesn't always turn up but most of the time it does.

Do you know how I can fix this problem

Regards

Jonas

\Jonas

Phil Bolduc

Trap the error, then bail out of the program:

  On Error Resume Next
  Set myXData = Application.InputBox( _
    prompt:="Select a range containing the X data.", _
    Title:="Select Chart Data", Type:=8)
  On Error Goto 0
  If myXData Is Nothing Then 
    ' user canceled 
    Exit Sub 
  End If

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

use inputbox to choose cells for plots in excel

Answer this question