 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / July 2004Global settings for all charts in a workbook
Hi all, LTNS (Yep, I'm back) - I have a workbook (XL97) containing 44 charts, each in its own worksheet. I would like to find a way to standardize the settings for all charts in the workbook. That is, I want to apply uniform formatting properties to all of the charts in the file without having to go to each and every worksheet and do it manually. For example, to format the horizontal & vertical axes to my preferences, I've been doing the very tedious Right-click -> Format-Axis and setting the tick mark patterns, font size & color, background color, etc. etc. manually for every! single! chart!. I'm pretty much a code virgin (my limited experience has only been with Access VBA anyway) & would rather not delve into the mysteries of behind-the-GUI Excel VB if it can be avoided. Can anyone offer any suggestions? tia, LeAnne If I were in your shoes, I would do one of two things. First, if all that is different about the charts is the data set shown, consider a single chart with the data selected by some kind of user choice -- basically, a variant of the Dynamic Charts page (http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html) Second, select a chart, turn on the macro recorder, do whatever changes you want to make, and turn off the macro recorder. Now, select each subsequent chart and run the macro. Or, share the code here and someone can show you how to change every chart on every worksheet.  SignatureRegards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2004 > Hi all, LTNS (Yep, I'm back) - > [quoted text clipped - 14 lines] > > LeAnne LeAnne - If everything is the same for all charts, including axis titles and chart titles, you can make one chart pretty, then select it and copy it, select the next chart, choose Paste Special from the Edit menu, Formats only. Repeat as needed. I never have charts that all have the same titles, though, so I whipped up this little macro, which stores the titles of each chart, then copies the first chart, pastes the formats onto the next chart, and puts the original titles back. To run it, start in Excel, press Alt+F11 to open the VB Editor. Choose Module from the Insert menu, and paste this code into the code window that opens. Go back to Excel, press Alt+F8, select the AllChartsTheSame macro in the listing (it's probably the only one listed if you don't write macros), and press Run. Sub AllChartsTheSame() Dim sXAxis As String Dim sYAxis As String Dim sTitle As String Dim cht As Chart For Each cht In ThisWorkbook.Charts If cht.Name <> ThisWorkbook.Charts(1).Name Then sXAxis = "" sYAxis = "" sTitle = "" If cht.HasTitle Then sTitle = cht.ChartTitle.Text If cht.Axes(xlCategory).HasTitle Then _ sXAxis = cht.Axes(xlCategory).AxisTitle.Text If cht.Axes(xlValue).HasTitle Then _ sYAxis = cht.Axes(xlValue).AxisTitle.Text ThisWorkbook.Charts(1).ChartArea.Copy cht.Paste Type:=xlFormats If Len(sTitle) > 0 Then _ cht.ChartTitle.Text = sTitle If Len(sXAxis) > 0 Then _ cht.Axes(xlCategory).AxisTitle.Text = sXAxis If Len(sYAxis) > 0 Then _ cht.Axes(xlValue).AxisTitle.Text = sYAxis End If Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ > Hi all, LTNS (Yep, I'm back) - > [quoted text clipped - 14 lines] > > LeAnne Hi Jon & Tushar, thanks for replying. Jon Peltier said: > LeAnne - > [quoted text clipped - 7 lines] > the first chart, pastes the formats onto the next chart, and puts the > original titles back. Wow, you threw that code together just like that? I'm impressed (maybe even a tad jealous!). It works like a charm, but I'm afraid it doesn't achieve the results I had in mind. You're correct that each chart has a different title. But some also have different scales on the y-axis, as well. To be more specific: the charts are box-and-whisker plots (created using your technique, Jon!) in groups of 4; 1 set of 4 box plots for a biological metric (say, EPT), and each of the 4 in the set showing metric results for a different ecoregion (EPT - Ecoregion 64, EPT - Ecoregion 66, &c). So each of the 4 ecoregion plots for a particular metric have their y-axes set to the same scale, but scales differ from metric set to metric set (e.g. the EPT charts are all set from 0-15 in increments of 5, but %EPT is set from 0.0 to 1.0 in increments of 0.10. This also means that the 4 plots in a metric set are labelled identically on the y-axis (e.g. "EPT Taxa", "PercentEPT"). When I run the module, it sets ALL of the y-axes for all charts in the workbook to the scale of the first chart (which happens to be EPT). And unfortunately, it also makes the boxes and whiskers go kablooey. Ideas? Thanks for your help, LeAnne Leanne - I just used a few lines of code to allow the quick and dirty copy/paste formats approach not to mess up the chart and axis titles. Works okay for what I intended it for. You would have to either enhance this macro to save the axis scale settings, and any other item that isn't the same from chart to chart, or write another macro that applies the same settings to each chart without touching the things that pasted formats destroy. To do this, you'd turn on the macro recorder while applying the settings you cared about, after everything else was ready. Then you would go through this code and remove references to things you didn't want included. Then adjust it to work on all charts, not just a selected chart. It goes a bit beyond a casual newsgroup post, but give it a go. When you hit a rough stretch, come back with questions. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ > Hi Jon & Tushar, thanks for replying. > [quoted text clipped - 33 lines] > > LeAnne > Leanne - > [quoted text clipped - 15 lines] > > - Jon Er...sorry about that. I was kinda hoping a fix would be relatively simple. But I can see this would be a fershlugginer piece of code, even for an MVP. And as I said, I'm a VB ignoramus...I don't really have the knowledge/skill to create a class module for all objects in the workbook's "charts" collection to set the named attributes of each object to my preferences (geez, it's painful just writing about it!). I'll just format the chart properties manually; it's only a one-time do, & it really won't take all _that_ long. Thanks again for trying to help, LeAnne LeAnne - It's not that painful. Record a macro, make it general, so it doesn't refer to a particular chart. then at the top of the macro, insert a few lines: Dim Cht as Chart For Each Cht in ActiveWorkbook.Charts then at the end, insert this line: Next (goes with For Each... above) Then make all the macro's references to ActiveChart refer to Cht instead. No ugly class modules needed, just the simple chart variable, Cht. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ > Er...sorry about that. I was kinda hoping a fix would be relatively > simple. But I can see this would be a fershlugginer piece of code, even [quoted text clipped - 8 lines] > > LeAnne |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.