Named range for chart range value?
 

Hello,
Trying to use VB only to use a combobox to select named range to graph. I
see lots of help, but all (I've seen so far) use formulas in cells. I'm
trying to accomplish the same using VB only.

Example: Dropbox to select which Quarter to report (named ranges = Qtr1,
Qtr2,..)
Qtr1 = sheet2, A1:C1 and so on...
How do I get "Qtr1" into the range value entry box for the range source?..
or can I ?
Thanks,
Randy

Named range for chart range value?
 

Hi Randy,

Set the ListFillRange of your ActiveX combobox to a cell range containing
your names. This might be a dynamic named range to expand with your named
Qtr's.

In combobox worksheet module -

Dim chObj As ChartObject
Dim sName As String
On Error Resume Next
sName = ComboBox1.Value
Set chObj = ChartObjects(sName)
On Error GoTo errH

If chObj Is Nothing Then
Set chObj = ChartObjects.Add(10, 10, 300, 150)
With chObj
.Chart.ChartType = xlColumnClustered
.Chart.SetSourceData Source:=Range(sName), _
PlotBy:=xlColumns
.Name = sName
.Chart.HasTitle = True
.Chart.ChartTitle.Characters.Text = sName
.Activate
.Chart.ChartArea.Select
End With
Else
chObj.Activate
chObj.Chart.ChartArea.Select
End If

Exit Sub
errH:
MsgBox "error"
End Sub

You will want to adapt this to your needs such as position & chart type etc.
Try the macro recorder for other chart things.

Regards,
Peter T

"RAP" wrote in message
...
Hello,
Trying to use VB only to use a combobox to select named range to graph. I
see lots of help, but all (I've seen so far) use formulas in cells. I'm
trying to accomplish the same using VB only.

Example: Dropbox to select which Quarter to report (named ranges = Qtr1,
Qtr2,..)
Qtr1 = sheet2, A1:C1 and so on...
How do I get "Qtr1" into the range value entry box for the range source?..
or can I ?
Thanks,
Randy

Named range for chart range value?
 

I omitted the procedure name with my copy/paste

Private Sub ComboBox1_Change()
Dim chObj As ChartObject
Dim sName As String
etc

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Randy,

Set the ListFillRange of your ActiveX combobox to a cell range containing
your names. This might be a dynamic named range to expand with your named
Qtr's.

In combobox worksheet module -

Dim chObj As ChartObject
Dim sName As String
On Error Resume Next
sName = ComboBox1.Value
Set chObj = ChartObjects(sName)
On Error GoTo errH

If chObj Is Nothing Then
Set chObj = ChartObjects.Add(10, 10, 300, 150)
With chObj
.Chart.ChartType = xlColumnClustered
.Chart.SetSourceData Source:=Range(sName), _
PlotBy:=xlColumns
.Name = sName
.Chart.HasTitle = True
.Chart.ChartTitle.Characters.Text = sName
.Activate
.Chart.ChartArea.Select
End With
Else
chObj.Activate
chObj.Chart.ChartArea.Select
End If

Exit Sub
errH:
MsgBox "error"
End Sub

You will want to adapt this to your needs such as position & chart type
etc.
Try the macro recorder for other chart things.

Regards,
Peter T

"RAP" wrote in message
...
Hello,
Trying to use VB only to use a combobox to select named range to graph.
I
see lots of help, but all (I've seen so far) use formulas in cells. I'm
trying to accomplish the same using VB only.

Example: Dropbox to select which Quarter to report (named ranges = Qtr1,
Qtr2,..)
Qtr1 = sheet2, A1:C1 and so on...
How do I get "Qtr1" into the range value entry box for the range
source?..
or can I ?
Thanks,
Randy