Create a new chart for every change of values in a combobox

[uriel78]

I've got a bar chart that takes values from cells A1:B5

In C1 and in D1 I've got two combobox (created with data validation method),
each one allows me to choose 4 different parameters. By changing these
values, the values in A1:B5 and the chart changes too. Now, for every
change of data in C1 and/or D1 I copy & paste the chart to another sheet; in
this situation I've got only ONE chart
Is there a way to obtain a *new* chart every time I change parameter in C1
and D1, so that I can avoid to do the copy & paste the single chart manually
(that is too much time-expensive) and put the it into another sheet ?

Thanks in advance

[Debra Dalgleish]

You could use an event procedure to do this. Add the following code to
the sheet module, as described here:

http://www.contextures.com/xlvba01.html#Worksheet

'=================================
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$1" Or _
Target.Address = "$D$1" Then
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range("A1:B5")
.Location Where:=xlLocationAsNewSheet
End With
Me.Activate
End If

End Sub
'====================================

uriel78 wrote:
> I've got a bar chart that takes values from cells A1:B5
>
> In C1 and in D1 I've got two combobox (created with data validation method),
> each one allows me to choose 4 different parameters. By changing these
> values, the values in A1:B5 and the chart changes too. Now, for every
> change of data in C1 and/or D1 I copy & paste the chart to another sheet; in
> this situation I've got only ONE chart
> Is there a way to obtain a *new* chart every time I change parameter in C1
> and D1, so that I can avoid to do the copy & paste the single chart manually
> (that is too much time-expensive) and put the it into another sheet ?
>
> Thanks in advance
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[uriel78]

Thank you very much, it runs !!!


"Debra Dalgleish" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> You could use an event procedure to do this. Add the following code to
> the sheet module, as described here:
>
> http://www.contextures.com/xlvba01.html#Worksheet
>
> '=================================
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address = "$C$1" Or _
> Target.Address = "$D$1" Then
> Charts.Add
> With ActiveChart
> .ChartType = xlColumnClustered
> .SetSourceData Source:=Range("A1:B5")
> .Location Where:=xlLocationAsNewSheet
> End With
> Me.Activate
> End If
>
> End Sub
> '====================================