How to update the Tkinter graph?

Excel - how do I graph multiple worksheet data

• This is a follow on to my previous question. I want to graph the contents of a static cell, B4, across multiple sheets in a workbook, such that when I add a new sheet each week, the graph updates. Please provide the means to do this on a sample workbook with just B4 filled out on several sheets. The graph should update when a new sheet is added that has a b4 cell filled in.

• Answer:

  Hello Mike, The macro listed below assumes you have the following sheets in your workbook: - Charts - to get the data extracted from the data sheets and produce a chart. It can be (should be) blank. - First - a blank sheet before the data sheets - one or more data sheets - Last - a blank sheet after the data sheets The steps necessary to add the macro to your work sheet is as follows: Tools -> Macro -> Visual Basic Editor At this point, you should get some additional windows and the visual basic editor will be running. If you don't have any existing macros, then Insert -> Module The title of the window should be something like Workbook1 - Module1 (Code) (replace Workbook1 with the name of your workbook) Copy / paste the macro below into this window. If you want a more descriptive name for the macro, change the first line which now reads Sub Headsetsdotcom() to something more descriptive like Sub GenerateGraph() Excel -> Close and return to Microsoft Excel This should bring up the spreadsheet again. You should only have to do the steps up to this point once. The macro will be saved with the workbook (and the data in the workbook). The first time, or whenever you add one or more data sheets, run the macro using Tools -> Macro -> Macros which brings up a dialog box. The macro below is named Headsetsdotcom [unless you renamed it], select it and click the Run button. The macro will automatically switch to the Charts worksheet, fill in the top section with the extracted data from the data sheets and add a line plot of the data. If you have problems up to this point - don't hesitate to ask for clarification on the steps, describing what steps worked and what did not. Now - the chart I produced might not be quite what you are looking for. In the comments below (lines starting with '), I have included a number of notes [numbers in square brackets] to describe how to customize the macro. If you use Tools -> Macro -> Macros then select the Headsetsdotcom macro and click on Edit, it should put you back into the Visual Basic editor. Scroll down to the notes and make changes as appropriate. [1] Column headings currently are E4 through E8; I expect you want some other names here. [2] If you add another Cell to plot (e.g., E9); add a line similar to the other ones. [3] Modify the chart type. You can use View -> Object Browser and enter xlcharttype as the search string (field at the top). The lower right scrolling window should show the choices of chart types. The names are pretty self explanatory but if you cannot find the one you want - describe the type you want in a clarification request. You might want to swap the rows / columns as well, use PlotBy := xlColumns instead. [4] through [6] Update the chart, X-axis, and Y-axis titles as needed. That should cover the customizations that I could think of. If this does not quite meet your needs - please clarify them so I can update the macro to better satisfy them. --Maniac Sub Headsetsdotcom() ' ' Headsetsdotcom Macro ' Macro recorded 3/29/2004 by Maniac ' ' ' Compute how many sheets we need to process ' While we are at it, copy the sheet names to the first column ' Sheets("Charts").Select FirstSheet = 0 NumberSheets = 0 For I = 1 To Sheets.Count If Sheets(I).Name = "First" Then FirstSheet = I End If If Sheets(I).Name = "Last" Then NumberSheets = I - FirstSheet - 1 Exit For End If ' add 1 to row number to skip first row Cells(I - FirstSheet + 1, 1).Value = Sheets(I).Name Next I Cells(1, 1).Value = "" ' ' Add the column labels to the top of the sheet ' [1] ' Replace the values below with the X labels desired ' on the generated chart ' Range("B1").FormulaR1C1 = "E4" Range("C1").FormulaR1C1 = "E5" Range("D1").FormulaR1C1 = "E6" Range("E1").FormulaR1C1 = "E7" Range("F1").FormulaR1C1 = "E8" ' ' Fill the rows with data copied from the sheets ' I loops through the rows / number of sheets ' [2] If you add cells to plot, modify the loop below ' For I = 1 To NumberSheets S$ = "=" + Sheets(I + FirstSheet).Name + "!" Cells(I + 1, 2).Formula = S$ + "E4" Cells(I + 1, 3).Formula = S$ + "E5" Cells(I + 1, 4).Formula = S$ + "E6" Cells(I + 1, 5).Formula = S$ + "E7" Cells(I + 1, 6).Formula = S$ + "E8" Next I Charts.Add ' [3] Modify chart type here, this is lines w/ markers ' Look up ChartType in the VBA Object browser for possible values ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("Charts").Range("A1:F4"), PlotBy:= _ xlRows ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = True ' [4] Update title as appropriate .ChartTitle.Characters.Text = "My Chart" .Axes(xlCategory, xlPrimary).HasTitle = True ' [5] Update x axis title as appropriate .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cell Reference" .Axes(xlValue, xlPrimary).HasTitle = True ' [6] Update y axis title as appropriate .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value" End With ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False Sheets("Charts").Cells(1, 1).Select End Sub