Chart automation

[VegasNath]

I have quite limited experience in designing charts in excel but I have been tasked with providing an automated charting solution in order to interrogate a large database. Does anyone have anything similar setup already, templates, tutorials, and / or suggestions for how best to approach?

[HansV]

Is it an OLAP (OnLine Analytical Processing) database, or a "normal" database?

[VegasNath]

It will be just an excel database, 4 columns (period, metric, value, company) but potentially many thousand rows. My initial thought is to create a pivot for each company with metric on the x axis and period on the y axis. Then use a vlookup grid to feed the chart (from the pivots) which would be changeable using various forms / activex controls. Maybe an amatuer approach....?

[HansV]

You could indeed use pivot tables and pivot charts. If you use report filter fields (page fields in Excel 2003 and before), you have an easy way of filtering the pivot table and pivot chart.

If you want to be able to chart data over different periods of time, you can use dynamic named ranges to change the source data for a chart dynamically depending on the value of a cell / dropdown, and you could use a scroll bar to change the time period.

[VegasNath]

Is it possible to have various charts looking to the same data source, but each chart only update based upon the value of a cell. For example, cell value = 1 which means that we are working with chart 1 (of maybe 14). Another range determines the metric required, so chart 1 updates, but the other charts maintain the metrics that were previously determined, when that cell value was not 1. IOW, I would like to avoid having 14 individual sources of data.

Hope this makes sense.

[HansV]

You could write VBA code to change the metric, and have it look at a specific cell to determine which chart to update.

[VegasNath]

Hans, would you be so kind as to provide a small example of how I could achieve this? Air code would be fine, just something to point me in the right direction.

[HansV]

The question is currently too vague, sorry. I'd need something to work with.

[VegasNath]

Hans, I have been working on a file attached, which will hopefully explain what I am trying to do. Please critique as you feel appropriate, it helps me learn

What I am trying to achieve is one user sheet showing 1, 4 or 9 charts as determined by the userform. Each chart is fed by one source range (admin sheet) to minimise overhead & maintenance. Only 1 chart should change at a time. I hope this is clear... Thanks

Added:
This is a stripped version, there will be more companies and more metric options. My plan is to further develop the userform so that companies can be selected / deselected, different chart types ete, etc..

[HansV]

You ask about the period end:

- Remove the code to populate ComboBox5 from the UserForm_Initialize event.
- Change the code for ComboBox4 and ComboBox5 as follows:

Code: Select all

Private Sub ComboBox4_Change()

    Dim rng As Range
    Sheets("Admin").Range("B5") = DateValue("1-" & Me.ComboBox4)

    'End period - How can we make this always greater than the starting period ??
    Me.ComboBox5.Clear
    For Each rng In Sheets("Admin").Range("ChooseDate")
        If rng.Value > Sheets("Admin").Range("B5") Then
            Me.ComboBox5.AddItem Format(rng.Value, "mmm-yy")
        End If
    Next rng
    Me.ComboBox5.ListIndex = 0

End Sub

Private Sub ComboBox5_Change()

    If Me.ComboBox5 = "" Then
        Sheets("Admin").Range("B6").ClearContents
    Else
        Sheets("Admin").Range("B6") = DateValue("1-" & Me.ComboBox5)
    End If

End Sub

[HansV]

You have a procedure Update that updates Chart5, but it isn't called anywhere.
Apart from that, you don't have any code that sets the source data for a chart...

[VegasNath]

You ask about the period end:

This is great, Thankyou! One question to avoid the debugger, how can I omit the last period from ComboBox4?

Also, if the userform is closed and I move to another sheet other than the chart sheet, I get a vba error 1004 on the ComboBox1_Change event. I have no idea why that code is even called at this point? How can I avoid this please?

[VegasNath]

You have a procedure Update that updates Chart5, but it isn't called anywhere.
Apart from that, you don't have any code that sets the source data for a chart...

This is the tricky bit that I am stuck with. I think that each change event should call the update macro (which identifies the source range based on combo's 4 & 5). I would like combobox2 to drive which chart is updated, chart5 was just the original chart I was using.

[HansV]

how can I omit the last period from ComboBox4?

Code: Select all

    'Starting period
    Dim i As Long
    For i = 1 To Sheets("Admin").Range("ChooseDate").Count - 1
        Set rng = Sheets("Admin").Range("ChooseDate").Cells(i)
        Me.ComboBox4.AddItem Format(rng.Value, "mmm-yy")
    Next i
    Me.ComboBox4.ListIndex = 0

[HansV]

Also, if the userform is closed and I move to another sheet other than the chart sheet, I get a vba error 1004 on the ComboBox1_Change event. I have no idea why that code is even called at this point? How can I avoid this please?

In the Deactivate event, you merely hide the userform, so it is still loaded:

Code: Select all

Private Sub Worksheet_Deactivate()
    UserForm1.Hide
End Sub

It'd be better to unload the userform:

Code: Select all

Private Sub Worksheet_Deactivate()
    Unload UserForm1
End Sub

[VegasNath]

Code: Select all

    'Starting period
    Dim i As Long
    For i = 1 To Sheets("Admin").Range("ChooseDate").Count - 1
        Set rng = Sheets("Admin").Range("ChooseDate").Cells(i)
        Me.ComboBox4.AddItem Format(rng.Value, "mmm-yy")
    Next i
    Me.ComboBox4.ListIndex = 0

Thanks Hans.

In the Deactivate event, you merely hide the userform, so it is still loaded:

Changing to Unload UserForm1: The UserForm_Initialize event is still called and the error still occurs.

[HansV]

As it is now, all charts use the same metric. If you want each chart to use its own metric, you will have to store the metric value in a separate cell for each chart instead of in one cell.
And in the UserForm_Initialize event, you should load the current settings instead of selecting 1 chart.
I'm not going to do that for you.
But in the attached version you can see how to set a specific chart. I have used a command button for this.

Book2.xls

[VegasNath]

Thankyou very much Hans, your help is greatly appreciated! As I work through this I may still have a few questions....

I am trying to remember the previous selections (from the admin sheet) when the userform is initialised. How can I ignore change events from within the initialise event? "Application.EnableEvents = False" seems to be ignored.

[HansV]

Declare a variable at the top of the UserForm module:

Private blnIgnoreEvents As Boolean

In the UserForm_Initialize event procedure, insert

blnIgnoreEvents = True

at the beginning, and

blnIgnoreEvents = False

at the end. In the various ..._Change event procedures, insert a line at the beginning:

If blnIgnoreEvents Then Exit Sub

[VegasNath]

Thanks!

I'm trying to create an array of the different chart names based on selected number of charts so I can loop through the chart names and assign them to cb2...

Code: Select all

Dim x1 as string
Dim x1a as integer
Dim y '?
Dim arr '?

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

Select Case x1a
    Case 1
        arr = ("Chart1")
    Case 4
        arr = ("Chart4_1, Chart4_2, Chart4_3, Chart4_4")
    Case 6
        arr = ("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
        'Me.ComboBox2.ListIndex = 0
        Me.ComboBox2.SelText = x2

I know I am having a stupid half hour but cannot get this right for the life of me! Please help.