+ Reply to Thread
Results 1 to 3 of 3

Named range for chart range value?

  1. #1
    RAP
    Guest

    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

  2. #2
    Peter T
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Peter T
    Guest

    Re: 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
    news:OBa#[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1