How to use a named range for chart data and series selection in VBA

[efmann]

Hello Everyone,

I'm trying to write a module that selects a data range for my chart worksheet and changes the title, subtitle, a textbox, and axis labels dependant on button clicks. I have sub routines that select which named range to use based on button clicks and set some of my global variables.

I've already set some global variables and determine rangename in another sub routine by rangename = [MyNamedRange] . rangename is set as a variant right now.

axislabel should be the first column of whatever named range I select.

rng should be a specific column that I select out of the named range (with bank and shift being integers to select that column).

Currently, Im getting held up on defining axislabel, rng, and textbox. I've tried several ways, except for the correct way. This is just the most recent iteration.

Thank you for whatever help you provide.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub SelectDataSeries()
'
'modifies chart to match button selections
'
Dim axislabel As String
Dim rng As String
Dim textbox As String
Dim clmn As Integer

clmn = bank + shift

With rangename
axislabel = offset(1, 1).RefersTo
rng = offset(clmn).Address
End With

textbox = [Text].offset(1, clmn - 1).RefersTo

If title <> "" Then 'prevent null title from deleting chart title
ActiveChart.ChartTitle.Select
selection.Caption = title
End If

ActiveChart.Shapes.range(Array("TextBox 1")).Select
If textbox = "" Then 'prevent null textbox from deleting text box
selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = textbox
Else
selection.Formula = textbox
End If

If subtitle <> "" Then 'prevent null subtitle from deleting subtitle text
ActiveChart.Shapes.range(Array("subtitle")).Select
selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = subtitle
End If

ActiveChart.SeriesCollection(1).Values = rng 'set series 1 to rng
ActiveChart.SeriesCollection(2).Values = rng 'set series 2 to rng
ActiveChart.SeriesCollection(1).XValues = axislabel 'set appropriate axis label
ActiveChart.SeriesCollection(2).XValues = axislabel 'repeat for series 2
ActiveChart.ChartArea.Select

End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Worf]

Hello and welcome to the Board

This is just an example that can be modified after your comments:

Sub SelectDataSeries()
Dim axislabel As Range, rng As Range, textbox$, clmn%, rangename As Range
Dim mytitle$, subtitle As Shape, subtext$
' tested with Excel 2003
clmn = 3
Set rangename = ActiveSheet.Range("d4:h10")
Set axislabel = rangename.Resize(, 1)   ' the first column
Set rng = axislabel.Offset(, clmn - 1)  ' the third column
ActiveChart.HasTitle = True
mytitle = "Chart Title"
subtext = "my subtitle"
If mytitle <> "" Then ActiveChart.ChartTitle.Caption = mytitle
' add text boxes, can be modified to deal with existing ones
ActiveChart.Shapes.AddTextbox(1, 10, 10, 50, 20). _
TextFrame.Characters.Text = "my text"
Set subtitle = ActiveChart.Shapes.AddTextbox(1, 10, 30, 80, 25)
subtitle.TextFrame.Characters.Text = subtext
With ActiveChart
    .SeriesCollection(1).Values = rng
    .SeriesCollection(2).Values = rng.Offset(, 1)
    .SeriesCollection(1).XValues = axislabel 'set appropriate axis label
    .SeriesCollection(2).XValues = axislabel 'repeat for series 2
End With
End Sub

 

[efmann]

Re: How to use a named range for chart data and series selection in VBA (SOLVED)

Thanks for your reply Worf,

I got the code working a couple of weeks ago, and after some tweaks and refining, I'm comfortable posting my solution up here.


my named ranges only encompass the data, not the data labels

'Global Variables:

Public rangename As String     'selects named range 
Public clmn As Variant         'selects named range column 

Public title As String         '\
Public subtitle As String      '|-variables for chart text objects
Public axistitle As String     '/


Sub SelectDataSeries()
'
'written by efmann
'
'modifies chart to match button selections
'

    Dim axislabel As String
    Dim rng As String, sumsheet As String
    
    If clmn = Empty Then          '\
        clmn = 6                  '|
    End If                        '|-Sets default values on open
    If rangename = "" Then        '|
        rangename = "ARange"      '|
    End If                        '/
    
    sumsheet = "'YourData'!"   'name of sheet containing named ranges
    
    axislabel = Range(rangename).Columns(0).Address 'set axis label address
    
    rng = Range(rangename).Columns(clmn).Address    'set chart data 
    
    If title <> "" Then 'prevent null title from deleting chart title
        ActiveChart.ChartTitle.Select
        selection.Caption = title
    End If
    
    If subtitle <> "" Then 'prevent null subtitle from deleting subtitle text
        ActiveChart.Shapes.Range(Array("subtitle")).Select
        selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = subtitle
    End If
    
    With ActiveChart
        .SeriesCollection(1).Values = sumsheet & rng        'set series 1 to appropriate range
        .SeriesCollection(1).XValues = sumsheet & axislabel 'set appropriate axis label
        .SeriesCollection(2).Values = sumsheet & rng        'set series 2 to appropriate range
        .SeriesCollection(2).XValues = sumsheet & axislabel 'set appropriate axis label
    
        If axistitle <> "" Then 'prevent deletion of axis title box
            .Axes(xlCategory).axistitle.Select   'change axis title
            .Axes(xlCategory, xlPrimary).axistitle.Text = axistitle
        End If
    
        .ChartArea.Select
    End With
End Sub

I set two series because it's the easiest way I know to have axis tick marks on both the left and right side of the chart. The global variable values are set by other subroutines that depend on button click, then call this main sub.

Hope it helps,

efmann