I am currently work on a macro that would be able to add a selected range on
to an existing chart. The problem i am running into is that while the macro
is running it will not allow me to select the desired chart to add the data
on. With input box function (Type = 8), it will let me select range on the
active sheet but will not let me select another sheet (ie chart) as range.

Would anyone be able to help me with this problem?

I use Excel 2000 and I have no difficulty selecting a range on another sheet
while the InputBox is displayed (assuming you are using Application.InputBox
and not the inputbox function).

"Ritesh" wrote:

I am currently work on a macro that would be able to add a selected range on
to an existing chart. The problem i am running into is that while the macro
is running it will not allow me to select the desired chart to add the data
on. With input box function (Type = 8), it will let me select range on the
active sheet but will not let me select another sheet (ie chart) as range.

Would anyone be able to help me with this problem?

Yea, I can select sheets...but i cant get the charts. I want to be able to
add the selected data on the chart.
any ideas?


"K Dales" wrote:

I use Excel 2000 and I have no difficulty selecting a range on another sheet
while the InputBox is displayed (assuming you are using Application.InputBox
and not the inputbox function).

"Ritesh" wrote:

I am currently work on a macro that would be able to add a selected range on
to an existing chart. The problem i am running into is that while the macro
is running it will not allow me to select the desired chart to add the data
on. With input box function (Type = 8), it will let me select range on the
active sheet but will not let me select another sheet (ie chart) as range.

Would anyone be able to help me with this problem?

I see... the InputBox only lets you return a range as the reference, not any
other object embedded on the sheet. So there are two things I can think of
doing:

1) using the InputBox idea, select a range that includes the upper left
corner of the chart, then step through the ChartObjects on that sheet to see
if the ChartObject.TopLeftCell is in the selected range:

Set SelectedRange = Application.InputBox("Select the cell containing the top
left corner of the chart:",,,,,,,8)
For Each ThisChartObj in SelectedRange.Parent
If Not(Intersect(SelectedRange, ThisChartObj.TopLeftCell) Is Nothing Then _
Set SelectedChart = ThisChartObj.Chart
End If
Next ThisChartObj

2) Create your own custom inputbox (i.e. userform) that prompts the user to
select the chart, then press a command button; from there your code would
determine the chart by the Selection - I am adding some error checking code
to make sure the selection is actually a chart!:

Public ChartObj as ChartObject, SelectedChart as Chart

Public Sub CommandButton1_Click()
On Error Goto NotAChart
Set ChartObj = Selection
Set SelectedChart = ChartObj.Chart
Exit Sub
NotAChart:
' Put code here to prompt user to redo selection
End Sub

"Ritesh" wrote:

Yea, I can select sheets...but i cant get the charts. I want to be able to
add the selected data on the chart.
any ideas?


"K Dales" wrote:

I use Excel 2000 and I have no difficulty selecting a range on another sheet
while the InputBox is displayed (assuming you are using Application.InputBox
and not the inputbox function).

"Ritesh" wrote:

I am currently work on a macro that would be able to add a selected range on
to an existing chart. The problem i am running into is that while the macro
is running it will not allow me to select the desired chart to add the data
on. With input box function (Type = 8), it will let me select range on the
active sheet but will not let me select another sheet (ie chart) as range.

Would anyone be able to help me with this problem?