Chart automation

[HansV]

Try something like this:

Code: Select all

    Dim x1 As String
    Dim x1a As Integer
    Dim y As Variant
    Dim arr() As String

    x1 = Sheets("Admin").Range("B2").Value
    x1a = Left(x1, 1)

    Select Case x1a
        Case 1
            arr = Array("Chart1")
        Case 4
            arr = Array("Chart4_1", "Chart4_2", "Chart4_3", "Chart4_4")
        Case 6
            arr = Array("Chart6_1", "Chart6_2", "Chart6_3", "Chart6_4", "Chart6_5, Chart6_6")
    End Select

    ....

    'Which chart are we manipulating
    For Each y In arr
        Me.ComboBox2.AddItem y.Value
    Next y

(It wasn't clear what x2 was, so I omitted it)

[VegasNath]

It does not like the syntax of the case statement... arr = Array("....."), RTE13, type mismatch, on all 3 lines.

[HansV]

Change the declaration of arr to

Code: Select all

   Dim arr As Variant

[VegasNath]

How can I say.... For each chart on ws, if chart.name is in arr, visible, else hidden?

[HansV]

First, hide ALL charts.
Then loop through the elements of the array and make the corresponding charts visible.

[VegasNath]

Hans, thank you, your help is much appreciated! This is what I have so far, I think I'm slowly making progress...... I am trying to change the chart type using a combobox without success. Please can you see why this is not working? Removing the 'On error resume next' results on a run time error 13.?


EDIT:
I figured this out so please ignore my question. Attachment updated for ref.

[HansV]

There's still a problem: the line

Code: Select all

        With ActiveSheet.ChartObjects.Chart(i).Axes(xlCategory)

should be

Code: Select all

        With ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory)

[VegasNath]

There's still a problem:......

Good catch

[VegasNath]

Hans,

I want to apply some formatting rules specifically to the Vertical & Horizontal axes, regardless of whether the Category / Value is on the vertical or vice versa? Is this possible please?

IOW...

vertical axes

Code: Select all

.TickLabels.Orientation = xlHorizontal

Horizontal axes

Code: Select all

.TickLabels.Orientation = -45

[/code]

So that I can switch between bar and column charts where the Category & Value switch axes, but still maintain a specific orientation of the labels.

[HansV]

Go to the Update routine in Module1. Just above "Next i" near the end, insert the following lines:

Code: Select all

        With ActiveSheet.ChartObjects(i).Chart
            If Typ = xlBarClustered Then
                .Axes(xlCategory).TickLabels.Orientation = xlHorizontal
                .Axes(xlValue).TickLabels.Orientation = -45
            Else
                .Axes(xlCategory).TickLabels.Orientation = -45
                .Axes(xlValue).TickLabels.Orientation = xlHorizontal
            End If
        End With

[VegasNath]

Thanks Hans, I was just about to post when I saw your reply. I solved this in a similar way. I already have a case statement which determines the chart type, to which I added VOri & HOri variables to drive the orientation of the labels. The same for all except the bar chart. If I could just engage the brain a little quicker......

[VegasNath]

In the Update routine, I have a variable ChartRng which can be either "All Charts", "Visible Charts" or the specific chart name, based on user selection. How can I adjust the following code to work with all, visible or specific?

Code: Select all

Sub Update()

Dim RwSta As Long, RwEnd As Long, Fmt As String, Srs As Integer, Typ As Integer, VOri As String, HOri As String
Dim Nm1 As String, Nm2 As String, Nm3 As String, Nm4 As String, Nm5 As String
Dim ChartRng As String
Dim ch As ChartObject
Dim i As Integer

'On Error Resume Next

    RwSta = Sheets("Admin").Range("C5")
    RwEnd = Sheets("Admin").Range("C6")
    Fmt = Sheets("Admin").Range("B7")
    Srs = Sheets("Admin").Range("B8")
    ChartRng = Sheets("Admin").Range("B3").Value
    
    'Chart Type specifics
    Select Case Sheets("Admin").Range("E3")
        Case "Col Clustered"
            Typ = 51
            VOri = "0"
            HOri = "-45"
        Case "Bar Clustered"
            Typ = 57
            VOri = "-45"
            HOri = "0"
        Case "Line Markers"
            Typ = 4
            VOri = "0"
            HOri = "-45"
        Case "Area Stacked"
            Typ = 1
            VOri = "0"
            HOri = "-45"
    End Select
    
    'Show Bank or Trust Names
    If Sheets("Admin").Range("E2") = "Bank" Then
        Nm1 = Sheets("Admin").Range("B10")
        Nm2 = Sheets("Admin").Range("C10")
        Nm3 = Sheets("Admin").Range("D10")
        Nm4 = Sheets("Admin").Range("E10")
        Nm5 = Sheets("Admin").Range("F10")
    Else
        Nm1 = Sheets("Admin").Range("B9")
        Nm2 = Sheets("Admin").Range("C9")
        Nm3 = Sheets("Admin").Range("D9")
        Nm4 = Sheets("Admin").Range("E9")
        Nm5 = Sheets("Admin").Range("F9")
    End If
    
    For i = 1 To ActiveSheet.ChartObjects.Count

        With ActiveSheet.ChartObjects(i).Chart
            .ChartType = Typ
            .SetSourceData Source:=Sheets("Admin").Range("A" & RwSta & ":F" & RwEnd), PlotBy:=xlColumns
            .Axes(xlValue).TickLabels.NumberFormat = Fmt
            .SeriesCollection(1).Name = Nm1
            .SeriesCollection(2).Name = Nm2
            .SeriesCollection(3).Name = Nm3
            .SeriesCollection(4).Name = Nm4
            .SeriesCollection(5).Name = Nm5
            .ChartTitle.Characters.Text = Sheets("Admin").Range("B4")
        End With
    
        With ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory)
            '.MinimumScaleIsAuto = True
            '.MaximumScaleIsAuto = True
            .BaseUnit = xlMonths
            .MajorUnit = Srs
            .MajorUnitScale = xlMonths
            .MinorUnit = 1
            .MinorUnitScale = xlMonths
            .Crosses = xlAutomatic
            .AxisBetweenCategories = True
            .ReversePlotOrder = False
            .TickLabels.Orientation = HOri
        End With

        With ActiveSheet.ChartObjects(i).Chart.Axes(xlValue)
            .TickLabels.Orientation = VOri
        End With
        
    Next i

End Sub

[HansV]

Aargh - I was just about to save the modified workbook when my Windows crashed, and the AutoRecovery version didn't contain the changes I had made.

Here is a version that allows you to manipulate individual charts. I split off a separate routine from Update to handle one chart.

Book5.xls

I tried to make the indenting of the code somewhat more consistent.

[VegasNath]

You are a star,

I want to be able to display / hide each individual series. Would you say this is best suited to option buttons or checkboxes or ?

[HansV]

Since you display 1, 4 or 6 charts, you'd have to hide/unhide option buttons or check boxes too, which is rather kludgy.
I'd use a multi-select list box. You can re-populate it the same way you re-populate a combo box when the user selects the 1, 4 or 6 charts option.

[VegasNath]

Where would I find one of those? I can see list box but multi-select?

Later:
Brain churning....
Why would I have to hide / unhide them. The quantity of companies would be the same regardless of how many charts in view?

[HansV]

Sorry, my bad. I didn't read your question carefully, and interpreted it incorrectly.

If you have a fixed number of series, a frame with check boxes will do fine. You shouldn't use option buttons here.
Check boxes are for situations where the user can select one or more options; option buttons are for situations where only one option can be selected.

[VegasNath]

I'm nearly up and running....
Please see attached: I have built the frame, labels and checkboxes. I have the labels changing from bank to trust names in line with combobox6, and have the initialize event sorted, but am now a little stuck.

1. How do I retain the true / false values for the initialize event?
2. Can I remove the comboboxes from the tab order?
3. What do I need to add to the update event to show / hide the series?

Thanks

[VegasNath]

I just found a small bug in combobox1 change event. The available options in combobox2 are derived from the value of combobox1 correctly. We remember the selection from cbox2 when we change cbox1 and then re-select it later, and rightly so. However, if cbox2 selection = Chart4_1 and we change cbox1 to 6 charts, Chart4_1 is no longer visible, so in that case should not be reset. I'm not sure of how to logically tackle this.?

[HansV]

1. How do I retain the true / false values for the initialize event?

The easiest way to do this is to set the ControlSource property of each of the check boxes to a cell, for example on the Admin sheet. That way, you won't need code to save/restore the values of the check boxes.

2. Can I remove the comboboxes from the tab order?

Yes, set their TabStop property to False.

3. What do I need to add to the update event to show / hide the series?

You must write code to remove the series when you want to hide them and to add them again when you want to show them. (Do you really want to do that?)