How to write a macro to modify an Excel chart

I want to "record" a macro that, for example, changes the line styles on an 
Excel chart.  I tried by selecting the chart, then clicking on "record 
macro", manually changing the line style, and then clicking on "stop 
recording".  But then when I tried to use the macro it wouldn't do anything.  
When I edited the macro there was no code, just the comment lines giving the 
shortcut.

Is it possible to use the "record" feature for macros that work with charts, 
or must one start with the VB editor?
Thanks,
Henry
-- 
leave well enough alone
0
7/12/2007 6:42:02 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
523 Views

Similar Articles

[PageSpeed] 50

Hi,

Here is the macro I get when starting in the spreadsheet:

Sub FormatChartLine()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlDash
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlAutomatic
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
End Sub

Starting on the chart produces something like this:

Sub Macro2()
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlDiamond
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
End Sub

What version of Excel are you using?

-- 
Cheers,
Shane Devenshire


"raymondvillain" wrote:

> I want to "record" a macro that, for example, changes the line styles on an 
> Excel chart.  I tried by selecting the chart, then clicking on "record 
> macro", manually changing the line style, and then clicking on "stop 
> recording".  But then when I tried to use the macro it wouldn't do anything.  
> When I edited the macro there was no code, just the comment lines giving the 
> shortcut.
> 
> Is it possible to use the "record" feature for macros that work with charts, 
> or must one start with the VB editor?
> Thanks,
> Henry
> -- 
> leave well enough alone
0
7/12/2007 7:30:02 PM
Oops!  Forgot to say Excel 2007.  Thanks for your suggestion.
Henry
-- 
leave well enough alone


"ShaneDevenshire" wrote:

> Hi,
> 
> Here is the macro I get when starting in the spreadsheet:
> 
> Sub FormatChartLine()
>     ActiveSheet.ChartObjects("Chart 1").Activate
>     ActiveChart.SeriesCollection(1).Select
>     With Selection.Border
>         .ColorIndex = 57
>         .Weight = xlThin
>         .LineStyle = xlDash
>     End With
>     With Selection
>         .MarkerBackgroundColorIndex = xlAutomatic
>         .MarkerForegroundColorIndex = xlAutomatic
>         .MarkerStyle = xlAutomatic
>         .Smooth = False
>         .MarkerSize = 5
>         .Shadow = False
>     End With
> End Sub
> 
> Starting on the chart produces something like this:
> 
> Sub Macro2()
>     With Selection.Border
>         .ColorIndex = 57
>         .Weight = xlThin
>         .LineStyle = xlContinuous
>     End With
>     With Selection
>         .MarkerBackgroundColorIndex = xlAutomatic
>         .MarkerForegroundColorIndex = xlAutomatic
>         .MarkerStyle = xlDiamond
>         .Smooth = False
>         .MarkerSize = 5
>         .Shadow = False
>     End With
> End Sub
> 
> What version of Excel are you using?
> 
> -- 
> Cheers,
> Shane Devenshire
> 
> 
> "raymondvillain" wrote:
> 
> > I want to "record" a macro that, for example, changes the line styles on an 
> > Excel chart.  I tried by selecting the chart, then clicking on "record 
> > macro", manually changing the line style, and then clicking on "stop 
> > recording".  But then when I tried to use the macro it wouldn't do anything.  
> > When I edited the macro there was no code, just the comment lines giving the 
> > shortcut.
> > 
> > Is it possible to use the "record" feature for macros that work with charts, 
> > or must one start with the VB editor?
> > Thanks,
> > Henry
> > -- 
> > leave well enough alone
0
7/12/2007 7:44:02 PM
Unfortunately, the Excel 2007 macro recorder doesn't record too much when 
you are editing charts (and nothing at all when you are editing shapes). I 
think they were out of time when 2007 was being released, and couldn't 
upgrade it for the new Office shapes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"raymondvillain" <raymondvillain@discussions.microsoft.com> wrote in message 
news:81065AA4-CE15-4190-AA0C-AD2E5DDEE4DD@microsoft.com...
> Oops!  Forgot to say Excel 2007.  Thanks for your suggestion.
> Henry
> -- 
> leave well enough alone
>
>
> "ShaneDevenshire" wrote:
>
>> Hi,
>>
>> Here is the macro I get when starting in the spreadsheet:
>>
>> Sub FormatChartLine()
>>     ActiveSheet.ChartObjects("Chart 1").Activate
>>     ActiveChart.SeriesCollection(1).Select
>>     With Selection.Border
>>         .ColorIndex = 57
>>         .Weight = xlThin
>>         .LineStyle = xlDash
>>     End With
>>     With Selection
>>         .MarkerBackgroundColorIndex = xlAutomatic
>>         .MarkerForegroundColorIndex = xlAutomatic
>>         .MarkerStyle = xlAutomatic
>>         .Smooth = False
>>         .MarkerSize = 5
>>         .Shadow = False
>>     End With
>> End Sub
>>
>> Starting on the chart produces something like this:
>>
>> Sub Macro2()
>>     With Selection.Border
>>         .ColorIndex = 57
>>         .Weight = xlThin
>>         .LineStyle = xlContinuous
>>     End With
>>     With Selection
>>         .MarkerBackgroundColorIndex = xlAutomatic
>>         .MarkerForegroundColorIndex = xlAutomatic
>>         .MarkerStyle = xlDiamond
>>         .Smooth = False
>>         .MarkerSize = 5
>>         .Shadow = False
>>     End With
>> End Sub
>>
>> What version of Excel are you using?
>>
>> -- 
>> Cheers,
>> Shane Devenshire
>>
>>
>> "raymondvillain" wrote:
>>
>> > I want to "record" a macro that, for example, changes the line styles 
>> > on an
>> > Excel chart.  I tried by selecting the chart, then clicking on "record
>> > macro", manually changing the line style, and then clicking on "stop
>> > recording".  But then when I tried to use the macro it wouldn't do 
>> > anything.
>> > When I edited the macro there was no code, just the comment lines 
>> > giving the
>> > shortcut.
>> >
>> > Is it possible to use the "record" feature for macros that work with 
>> > charts,
>> > or must one start with the VB editor?
>> > Thanks,
>> > Henry
>> > -- 
>> > leave well enough alone 


0
jonxlmvpNO (4558)
7/13/2007 5:41:11 PM
Hi,

In that case its going to be more work because you will need to write the 
macro manually.

-- 
Cheers,
Shane Devenshire


"raymondvillain" wrote:

> Oops!  Forgot to say Excel 2007.  Thanks for your suggestion.
> Henry
> -- 
> leave well enough alone
> 
> 
> "ShaneDevenshire" wrote:
> 
> > Hi,
> > 
> > Here is the macro I get when starting in the spreadsheet:
> > 
> > Sub FormatChartLine()
> >     ActiveSheet.ChartObjects("Chart 1").Activate
> >     ActiveChart.SeriesCollection(1).Select
> >     With Selection.Border
> >         .ColorIndex = 57
> >         .Weight = xlThin
> >         .LineStyle = xlDash
> >     End With
> >     With Selection
> >         .MarkerBackgroundColorIndex = xlAutomatic
> >         .MarkerForegroundColorIndex = xlAutomatic
> >         .MarkerStyle = xlAutomatic
> >         .Smooth = False
> >         .MarkerSize = 5
> >         .Shadow = False
> >     End With
> > End Sub
> > 
> > Starting on the chart produces something like this:
> > 
> > Sub Macro2()
> >     With Selection.Border
> >         .ColorIndex = 57
> >         .Weight = xlThin
> >         .LineStyle = xlContinuous
> >     End With
> >     With Selection
> >         .MarkerBackgroundColorIndex = xlAutomatic
> >         .MarkerForegroundColorIndex = xlAutomatic
> >         .MarkerStyle = xlDiamond
> >         .Smooth = False
> >         .MarkerSize = 5
> >         .Shadow = False
> >     End With
> > End Sub
> > 
> > What version of Excel are you using?
> > 
> > -- 
> > Cheers,
> > Shane Devenshire
> > 
> > 
> > "raymondvillain" wrote:
> > 
> > > I want to "record" a macro that, for example, changes the line styles on an 
> > > Excel chart.  I tried by selecting the chart, then clicking on "record 
> > > macro", manually changing the line style, and then clicking on "stop 
> > > recording".  But then when I tried to use the macro it wouldn't do anything.  
> > > When I edited the macro there was no code, just the comment lines giving the 
> > > shortcut.
> > > 
> > > Is it possible to use the "record" feature for macros that work with charts, 
> > > or must one start with the VB editor?
> > > Thanks,
> > > Henry
> > > -- 
> > > leave well enough alone
0
7/14/2007 3:38:03 PM
Reply:

Similar Artilces:

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

transparent areas in charts?
How do you create transparent areas in imbedded charts? (for Excel 2003). When I click on "Format Chart area", then "Fill effects", there is a place where you can change the transparency settings, but it is faded out and will not work. Any Ideas? You can make most elements totally transparent. Ignore the transparency button, which is only for decoration. Instead, if you set an element's area to none, the element will be transparent. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech....

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

Macro Help 11-24-09
I have one workbook of data (1 tab) that has data for 20 different Sales Reps (different names). I need to copy all data for "Rep A" into a separate worksheet, and same for "Rep B" and so on. At the end I would have 1 tab for all data and 20 tabs with the data for each rep. Basically, I need to copy and paste each rep data into a new worksheet within the same workbook but didn't want to do it manually. I hope this makes sense. See Ron de Bruin's site for code. http://www.rondebruin.nl/copy5.htm Also check out his easyfilter add-in. http://www.ro...

Item Description--Modifier?
Hello: I have a GP 10.0 client that needs to extend the Inventory Item Maintenance Description field even further than it is now. The keyable length of this field is 100. They need to see a length of at least 125 characters. And, they want to see this "bad boy" all on the Item Maintenance window. Can Modifier with VBA accomplish this? Thanks! childofthe1980s Hi You can use the modifier & VBA with the DUOS to extend fields. There are some caveats: 1) As you cannot change the table structures, this method does not work for the primary key field. 2) If you use thi...

SpreadSheetML (XML for Excel)
Hello All, A client of mine has Office X for Mac. I am wondering if this edition of Excel supports SpreadSheetML. I have heard that it also support the new Open XML formats (same as Office 11). Does anyone know if either of these statements is accurate? Thanks in advance, -KJ Hi KJ: I believe that both statements are wrong. The XML converter for Office Next on the Mac has not yet been developed. It's not likely to come until after the PC Office team ships their product to retail -- until they stop CHANGING the thing :-) My guess is that the converter won't appear until sometime...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Modifying user defined data type
Hi How can I modify a user defined data type which is assigned to several fields across db? Thanks Regards You can't, I'm afraid. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "John" <info@nospam.infovis.co.uk> wrote in message news:#IntyI3xKHA.2644@TK2MSFTNGP04.phx.gbl... > Hi > > How can I modify a user defined data type which is assigned to several > fields across db? > > Thanks > > Regards > > > Basically, what Tibor...

Letter Writing assitant to be used with Prospects
Would like to see Letter Writing Assitant to be extended to be used with Prospects. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=73b8a531-e5c3-4d8b-91ae-5b1758b9f1a5&...

View original Excel after saving over it
In error, i hit save instead of save as. I'd like to view the original documents contents. is this at all possible. i use windows xp. excel 2003 Not very likely. Try a Google search action if you haven't already, but I think it's impossible. This underscores the importance of backups. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "houb" wrote: > In error, i hit save instead of save as. I'd like to view the original > documents contents. is this at all possible. i use windows xp. excel 2003 In article <C2A3F0E7-24E0-43A5-809A-ECA719...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

ExCel programming
I am working on a spreadsheet for work where it will calculate commissions to be earned based on my sales. There is a table that I have to use to determine what the percentage would be, how would I write an equation for that. For example, the equation would have to say: if say D3 was = or < than %, use cell# whatever and then have a sum in there. The table is on another sheet in the document so that would have to be there too. Basically, the way that my commissions are calculated is that we have to take what I sold in any given month and find out what percentage of my yearly plan i...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...