Never mind I solved it, see red marked for solution



I want to let the user add a serie to a chart. While doing so the usr will collect data using Inpubox from a different worksheet. He will need to go there three times to collect all data. I would therefore want the code to recognize on which worksheet the usr collected the first data set on and then when the usr has collected the x-values I want the macro to return to that sheet so the usr can collect the Y-values and finally return to collect the DataLabels.

I have earlier here been tiped of

Dim ws As Worksheet
Set ws = ActiveSheet

which has worked well earlier but now it lets me down. Can anyone help me with this Below is the code to collect all data. The InputBoxes appear on the sheet containing the chart and the usr has to manually switch sheet to the one with the data.

Dim Ch As Chart

Set Ch = ActiveChart

 Dim ws As Worksheet

''' Collect X and Y values
    Set myXData = Application.InputBox( _
        prompt:="Select a range containing the X data.", _
        Title:="Select Chart Data", Type:=8)
''' here I want the code to recognize on which sheet the x data are collected '''from. And then return to this sheet to collect y data

   Set ws = myXData.Worksheet

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


''' Collect legends
        Application.DisplayAlerts = False
            Set myLegends = Application.InputBox( _
                prompt:="Select legends if wanted else press cancel", _
                    Title:="Select Data Labels", Type:=8)


Now I have figured that    Set ws = myXData.Worksheet gives the worksheet. I have used this before the last to green marked lines which lets the wsheet with data remain as the active. However with that I can't make the sheet with the chart the active sheet again. Any suggestion on this

Grateful for help with this.


Re: Using Inputbox to detect a worksheet

Andy Pope


How about this to check and active chart depending on whether it is a chart object or not

If TypeName(Ch.Parent) = "ChartObject" Then
End If