Need Help with Dynamic Chart Worksheet Change Macro

[clmarquez]

I have the following code, but can't seem to correct the error generated with the line that sets the source data. Can someone help me out? I have data in columns A and B, but the # of rows vary based on a combobox pulldown value. I would like the chart to update automatically with the # of rows that get populated with data. Thanks in advance for any help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
Dim MyRange As Range

LastRow = Range("A1").End(xlDown).Row
Set MyRange = Range("A1:B" & LastRow)

If Not Intersect(Target, MyRange) Is Nothing Then
ActiveSheet.ChartObjects("Chart 3").SetSourceData _
Source:=Sheets("Sheet2").Range(MyRange)
End If

End Sub

[Andy Pope]

Hi,

You need to alter the Source range and you also omitted the .Chart
object reference.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
Dim MyRange As Range

LastRow = Range("A1").End(xlDown).Row
Set MyRange = Range("A1:B" & LastRow)

If Not Intersect(Target, MyRange) Is Nothing Then
ActiveSheet.ChartObjects("Chart 3").Chart.SetSourceData _
Source:=MyRange
End If

End Sub

Cheers
Andy

clmarquez wrote:
> I have the following code, but can't seem to correct the error generated
> with the line that sets the source data. Can someone help me out? I
> have data in columns A and B, but the # of rows vary based on a
> combobox pulldown value. I would like the chart to update
> automatically with the # of rows that get populated with data. Thanks
> in advance for any help.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim LastRow As Long
> Dim MyRange As Range
>
> LastRow = Range("A1").End(xlDown).Row
> Set MyRange = Range("A1:B" & LastRow)
>
> If Not Intersect(Target, MyRange) Is Nothing Then
> ActiveSheet.ChartObjects("Chart 3").SetSourceData _
> Source:=Sheets("Sheet2").Range(MyRange)
> End If
>
> End Sub
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[clmarquez]

Thanks Andy.

[Andrew Taylor]

It's possible to create dynamic charts without using macros. See
for example:

http://www.tushar-mehta.com/excel/ne...ynamic_charts/



clmarquez wrote:
> I have the following code, but can't seem to correct the error generated
> with the line that sets the source data. Can someone help me out? I
> have data in columns A and B, but the # of rows vary based on a
> combobox pulldown value. I would like the chart to update
> automatically with the # of rows that get populated with data. Thanks
> in advance for any help.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim LastRow As Long
> Dim MyRange As Range
>
> LastRow = Range("A1").End(xlDown).Row
> Set MyRange = Range("A1:B" & LastRow)
>
> If Not Intersect(Target, MyRange) Is Nothing Then
> ActiveSheet.ChartObjects("Chart 3").SetSourceData _
> Source:=Sheets("Sheet2").Range(MyRange)
> End If
>
> End Sub
>
>
> --
> clmarquez
> ------------------------------------------------------------------------
> clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386
> View this thread: http://www.excelforum.com/showthread...hreadid=500034