Excel VBA Graph[urgent]

[danielngoo]

I did manually create a graph on a sheet called "Graph" according to a sheet called "Project Manager". Each project has a graph with their detail to show their performance.

IN VBA part, I did create a button to add project manager to add and delete the Project Manager. Now the problems is, when i delete a project manager, the Graph do not intelligent enough to update the source in the graph to appropriate Project Manager, now all the Graph information is wrong because they are refering to address, not to correct Project Manager.

Is there anyway to solve this problems? Thanks for helping.

[Ecniv]

If you do not need to have lots of graphs, perhaps a look up list of project managers on one sheet, with appropriate ranges, and one graph with a list (validation?) on a cell to choose who to view.

As to your problem, if you flowchart how your sheet reacts when you delete a project manager, how are you going to choose the source for the data?

[danielngoo]

ya, correct, if i delete a project manager, how intelligent the VBA would be to help me to choose the appropriate source of date, you get what i mean?

How to look up in the Graph and choosing the correct Project Manager

What should i put in Souce of Data in the VBA to able me to choose the source in another sheet and return whole row back to the source of data in Graph.

Let's say the graph is in Graph Sheet, the project manager is in PM Chart, row A, and the source chart will be in PM Chart row C to row N which is from january to December performance result.

[danielngoo]

Could anyone please help me? Urgent....

[westconn1]

i did a macro to update a graph, so that as more data was added it would look for the end of the data and reset the chart data,

if that can be any help to you i gan get you that code

pete

[danielngoo]

that would be nice if you post up the code. ... thanks westconn1

[westconn1]

Private Sub Chart_Activate()
Dim d As Date, d1 As Date
 
If actve Then actve = False: Exit Sub
actve = True
 
     Worksheets("Sheet1").Visible = False
     d = Format(DateAdd("m", 1, Date), "mm/yyyy")
     d1 = Format(DateAdd("yyyy", -1, Date), "mm/yyyy")
     Worksheets("Sheet1").Activate
     ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(0, -1).Select
 
   Do While ActiveCell.Text = ""
   ActiveCell.Offset(-1, 0).Select
   Loop
   ac = "A1:" & ActiveCell.Address
Worksheets("Sheet1").Visible = True
Charts(1).Activate
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("a1:" & ac)
    With ActiveChart.Axes(xlCategory)
        .MinimumScale = d1
        .MaximumScale = d
 
    End With
End Sub

this may give you some clues, i am sure i could manage to improve on this a lot now, to when i did it some years ago, but hav no real need to do so. and i had to stop this from being recursive, actve was declared at form level

pete

[danielngoo]

thsi is able to change one graph or many graphs?

because at PM CHART sheet, there are many diffrernt graphs indicating diffrent managers ..

from what i understand from the code, it chooses the active chart, right ?

[westconn1]

this is just a sample i did some time ago, for a single graph on it's own sheet, some of the code should be adaptable to your applicaion

pete

[danielngoo]

For a single graph i think can be used, but my problem now is how to choose a single appropriate Graph from many Graphs then to another Graphs in a sheet ?

[danielngoo]

or i am thinking of another alternative, in the Source Data of graph, what is the correct command to LOOKUP ?

Example, i want to search "Daniel" in 'Family - Member' sheet B10:B30, return value of 'Family - Member' sheet D10:O30,

That means when i finished searching Daniel and found it, it will return row data back to another graph sheet.

[danielngoo]

anyone knows whether can LOOKUP function can be used in Graph source data ?

[westconn1]

That means when i finished searching Daniel and found it, it will return row data back to another graph sheet.

that is basically what the code i posted does, finds the end of the data, then returns that row to the source for the graph, but if you want to do it for several graphs you have to do it for each.

pete

[danielngoo]

What i mean is in the Data Souce itself in the template, it's not the Visual Basic Code

[westconn1]

i don't understand what you are trying to do now, i thought you wanted the graphs to load depending on the data

post your spreadsheet if you want me to have a look, mark which part is not working for you

pete

[danielngoo]

ok, forget all my problems previously.

If i want to create a graph, in the source of data, can i use LOOKUP?
Name: ="Demand FTE"
Values: = LOOKUP ( Can look up being use here)

[westconn1]

if you put you chart data in to named ranges this will work to add new charts, it could be adapted to change data range on existing as well

Dim n As Name, rng As Range, i As Integer
 
For Each n In Names
    Application.Goto Reference:=n.Name
    
    Set rng = Application.Selection
    
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=rng 'Sheets("Sheet1").Range("B5:B13")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
s = Right(ActiveChart.Name, Len(ActiveChart.Name) - 7)
        ActiveSheet.Shapes(s).IncrementTop i * 220
    i = i + 1
Next

if you need more help, i think i woould need to see a sample of your workbook
sometimes that is so much easier

pete

[dale_albiston]

You *may* not even need VBA for this, while you cannot use functions directly within the graph formula, you can use named ranges.

big 'gotcha' ALL references in the graph formula MUST be exact, e.g. $A$1 not A1. thus any formula MUST return absolute references.

Hint: build the formula up in stages, if your data is in multiple sheets, have one cell on a page which can be set to be equal to the sheet name for the data you require (you can now include this in the function to specify the formula).

All you really need to do, is locate the start of your data, and work out how long the range is. now for the complex bit. The formula to actually build each range is nasty, and should be built on a worksheet, since once in the named range box its virtually impossible to edit it (since using the cursor keys causes trouble).

I can't remember the exact formula, but its based on a mix of MATCH(), OFFSET() and ADDRESS(). theres no problems using a VLOOKUP() or anything esle to throw a parameter into the mix.

start small. Hard code your range to a named range and plot the graph from that.

then modify the formula to use ADDRESS() with coded parameters, then replace these parameters with forumlas. the idea is that when you make a mistake (you will, its nasty) you can work out where.

Its worth having the formula (which will return an array) defined in a cell, then have another cell with ROWS() pointing to the named range to monitor how its working.

With a bit of work you can have ALL your charts controlled by a few pick lists.

e.g. a start point, and how many days to look back controlling you X-Axis, and another box picking which page you look at. Now you can apply your formatting once.

for multiple charts at once, well define the ranges more than once (once for each graph) and either have multiple control ranges or hard code some parts.

I'll have to dig the example sheet up.

(I got sick of updating about 50 ranges each week so spent two days head scratching this one out - it REALLY SHOULD be in the help file, or failing that allow the charting engine to use relative references like everything else can)