Series overlap/gap width with VBA

Does anyone know how to set the series overlap and gap 
width using VBA?  Have looked through all the regular 
reference material (including KB search) and can't find 
any way to refer to these properties programatically.

Thanks!
K Dales
0
anonymous (74722)
4/13/2004 1:50:37 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1606 Views

Similar Articles

[PageSpeed] 47

Hi K,

The macro recorder is a good way to discover these properties.

It produced this code for me.

     With ActiveChart.ChartGroups(1)
         .Overlap = 20
         .GapWidth = 50
     End With

Cheers
Andy

K Dales wrote:

> Does anyone know how to set the series overlap and gap 
> width using VBA?  Have looked through all the regular 
> reference material (including KB search) and can't find 
> any way to refer to these properties programatically.
> 
> Thanks!
> K Dales

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/13/2004 2:06:07 PM
Thanks Andy - should have thought of the Macro recorder 
myself!


>-----Original Message-----
>Hi K,
>
>The macro recorder is a good way to discover these 
properties.
>
>It produced this code for me.
>
>     With ActiveChart.ChartGroups(1)
>         .Overlap = 20
>         .GapWidth = 50
>     End With
>
>Cheers
>Andy
>
>K Dales wrote:
>
>> Does anyone know how to set the series overlap and gap 
>> width using VBA?  Have looked through all the regular 
>> reference material (including KB search) and can't find 
>> any way to refer to these properties programatically.
>> 
>> Thanks!
>> K Dales
>
>-- 
>
>Andy Pope, Microsoft MVP - Excel
>http://www.andypope.info
>.
>
0
anonymous (74722)
4/13/2004 2:24:34 PM
Reply:

Similar Artilces:

i do not see the "plot series on" option in Excel 2007
Hi, I am not able to see the "Plot Series on" Option under Format Data Series / Format layout in Excel 2007. Is there any configuration that I need to do? I want to add a Secondary vertical axis Thanks, Maithily I don't have Excel 2007, but the first question is whether you have more than one series. In 2003 it will grey out that option on the Format Data Series/ Axis tab unless you have multiple series in the graph. -- David Biddulph "Maithily" <Maithily@discussions.microsoft.com> wrote in message news:8B77753E-FAA7-4A49-A470-9FCA7BFBB1A9@microsoft.com...

Bar width on existing chart changes when changing source data
I have a spreadsheet with a bar chart already in it (it's showing the number of quotes sent monthly w/ trendlines). In column A, I have a month-year and in column B, I have the number of quotes sent in that month. I changed the source data to display a different set of months, and the bar widths narrowed down to the width of a line. I undid the change and the bar widths went back. I then tried to edit the chart formula one cell at a time and found that when I reached a particular month forward in time, that that's when the change occurred (row 40). I thought maybe it was ...

COUNTIF in VBA #2
I have a userform which adds records to sheet 1. One of the textboxes is called reference and when i change the contents of this i want to make sure said contents arent already present in sheet1,col B. So i see then that i need to use COUNTIF. To check if the entry in A1 appears once in col B i used =COUNTIF(B:B,A1)=1 To check if the entry occurs more than once in the list in col B do i use =COUNTIF(B:B,A1)>=1 And how do i crowbar this into vba? For VBA im thinking something along the lines of formula = "=COUNTIF(B:B,me.reference.value)>=1" but them im lost on the message...

VBA Macro Error
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I�m running in a pretty bad problem: If I write in MSOffice 2008 a number followed by a letter like "5a" on a new line I get an error "This command is not available in this Version of Microsoft Word" It seems that word want�s to make a lookup for a VBA script - which is not supported anymore, so in panic they write the message on the screen. But instead I really want to write only 5a. Is there a button to switch of those interpretations? It was not an issue with 2004 and not on windows. Any...

Fix column width with when sychronizing a list to a Sharepoint sit
When I synchronize my Excel file with a Sharepoint web site, the column widths change (i.e. get wider) in an attempt to allow all of the text (wrappred) to be visable. This repeats itself with every saving iteration which makes the file to big to print and I then have to manually reset the column widths. Is there a way I can keep the column width the same when synchronizing? Or, alternatively, is there a way I can easily restore the column widths without have to manually resize them? Thanks, Bob. Click on the Data. then, get the External Data Toolbar, if it didn't pop up on its own...

VBA Crashes Outlook Express
Hi All I've got a WorkBook form a college, and for some reason, it makes O crash. When i try to open OE it does open, but it takes forever. W have tried it on different computers, and OE crashed all the time. All the code does is prevent users from changing some values, hid sheets, set up print options. It removes the original Menu Bar and replace it with another, but it' all set back Before_Close. It contains: Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False but their all set back to True Before_Close. Anyone else who has come...

Need help with adjusting width of VBA-created toolbars
Hello, I am using VBA to create a series of toolbars, which are then assigned to macros. The problem I am having is: If I create a toolbar manually, it automatically adjusts its width to whatever caption is specified. However, when I create a toolbar with VBA, the width does not adjust itself to the caption, so the toolbar is wider than neccessary. Here is an example of the code I am using: Sub newButton3() On Error Resume Next CommandBars("C").Delete Dim cBar, ccBar CommandBars.Add.Name = "C" Set cBar = CommandBars("C") Set ccBar = Com...

Change default zoom value to page width in SSRS 2008
Is it possible to change the default zoom value from 100 to page width? thank you. ...

Keeping Chart Series Formats whilst sorting
Hi, I have created a bubble chart where I have set specific colours to each series. However if I then sort the source table the formating doesn't follow and they all change. Any way to fix the format so this doesn't happen and I keep consistent colours irrespective of sorting the source data ? regards Greg -- Fullam ------------------------------------------------------------------------ Fullam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33363 View this thread: http://www.excelforum.com/showthread.php?threadid=538391 ...

Access 2007 VBA problem
Moved database from Access 2003 to Access 2007. Now every so often (too often) I get a message saying "The database cannot be opened because the VBA project contained in it cannot be read." It goes on to say that the VBA in it needs to be deleted, ect. Does anyone have any solution for this problem and why it is occurring? Help!! Nick, a decompile might be in order here. Here's a standard sequence for sorting out a raft of possible issues. Try it in order: 1. Uncheck the boxes under: Tools | Options | General | Name AutoCorrect In Access 2007, it's: Offi...

VBA and Excel help
I'm not a newbie at this but it has been so long I feel lost like one :) I need to construct a routine for the following: I have created a travel voucher form that has a 1300+ list of data of virtually every city in the country, many with multiple entries such as: WILLIAMSBURG, VIRGINIA, 1 Apr through 31 Aug WILLIAMSBURG, VIRGINIA, 1 Sep through 31 Mar Using data validation (list) as an input method, for each day traveled (from 1 day to 4 weeks, where each day can be a different location), the user inputs the travel location by clicking on the data validation list box drop arrow in the...

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

VBA function changing name
OK, here's a strange one - well strange to me anyway. I've declared a function in a module using mixed case: Function TMDE_Category (FormName As Form) The function is called by the VBA code behind two different forms. I noticed the other day that it appeared in the module as Function tmde_category(FormName As Form) Obviously, this isn't preventing the database application from running, but I can't figure out why it changed. I changed it back to the mixed case declaration, saved the module, exited the app, reopened it and looked. The function had changed back t...

Chart series #2
Hello! I have previously posted questions in this field but I have never gotten an answer. I really really need help with this. I have never programmed before and now I am supposed to do this VBA application for Excel. What I want to do is simple enough but I have trouble implementing it and since and really do not know anything about programming I do not know what is wrong. The problem is that I am supposed to replace the existing series for a chart with new series. I have the new series but when applying them to a new chart it does not work if I have not manually erased the old chart ...

Office2004 crashes in VBA
Hi All I'm having a problem with my office 2004, it crashes when I forget to make a space infront of a ( in the VB editor. Office force quits, and tells me that it will recover my data, but it doesn't do so... I'm not that experienced with the VB editor, but it seems ti me that office should only give me a warning, and let me correct the mistake - or is it supposed to quit? Hope you can help me with a solution to this problem regards Hi Janus: It's not supposed to quit. I don't think the space is the problem. Show me the whole code module and I will have a better...

how to do these things in Excel with and without VBA?
Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here...

VBA Find a Replace
After standing the test for over a year, a problem with my VBA Find and Replace AddIns was indentified by a user in Moscow Russia yesterday. If you remember, the last problem identified and fixed was how to find text in the shape range of any shape located in the header/footer story ranges of a document. Through ignorance and lack of comprehensive testing, I failed to consider grouped shapes, individual shapes in a drawing canvass, and grouped shapes in a drawing canvass that are in a header\footer story range. By adding a new recursive sub-routine this issue is now also fixed...

Add Whisker to floating series that I put NO FILL
How can I find a series on a graph to add whiskers to if I have highlighted the series and made it disappear by selecting no fill? Sort of making my own stock chart, but can the hidden series be found? Thanks for your help! You can find it in the Chart Element dropdown on the Chart toolbar, or you can select another series and click the up and down arrow keys until the hidden series is selected. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "John" <John@discussions.microsoft.com> wrote in message news:1F0...

Data series label in Stacked Chart
Let me explain by an example: I am making a Stacked Chart for 1st Quarter of Sales from dffierent Staff Month Staff Sales Jan A 100 Jan B 200 Feb A 400 Mar C 100 Mar D 600 Staff B has record in Jan only. When I choose to show the data series on the Stacked Chart, the name of B appear on Feb and Mar also at 0 level. I want the name of Staff B Data series label only appear in Jan. How can the chart show the data series label which has data(sales) only? ...

java message with VBA
You can import www site with Excel, but only those parts which writes in HTML. Java applet seems to disappear. Is there any possibility to intercept the java message (text) with VBA <SCRIPT LANGUAGE="JavaScript"> function choiceCheck() { var nrTable=new Array("119b/05",-1); var timeTable=new Array("07:16","09:50",-1); var dateTable=new Array("23-06-2005","23-06-2005",-1); � so on This part of data will not appear Any suggestion Thanks Don't see how, as that is an instruction to HTTP to run that c...

loop VBA request
I post below earlier on but I couldnt see the thread, if duplicated please exscute me. I have 3 columns contain names(unknown, may duplicated and dynamic row), date and amount; I am looking a way to get a sum and count on data that: 1) date is equal to or greater than today, then 2) loop thru name and subtotal on each customer 3) sum subtotal if value is equal or above 10k 3) count 2) with same name treat as once Tks Seeker: Your post is not entirely clear.. notice that you have had no responses. If the customers are not sorted in order, then you will have to loop through t...

copy and paste from excel to ppt with VBA
Hello all, I need to copy a lot of graphs from excel and paste them into a ppt file using VBA code. So far, I used this VBA code: ... Worksheets("Slide01").Range("BB1:BF1").CopyPicture xlScreen, xlBitmap PPSlide.Shapes.Paste.Select .... and format of the object in the ppt is a bitmap. Now I would like to copy the object like a "picture". How must I change the above VBA code in order to do that? (please consider that the presentation has a lot of slides, so it should remain "light") Thx a lot for your help Patrik -- PATRIK --------------------...

Time Series
I have about 30000 lines of data that looks like so: Time Count 93000 447 93001 1020 93002 1438 93003 2177 93004 3042 93005 3467 93006 2913 93007 3341 93008 3547 93009 2908 93010 3512 93011 4090 Where ColA is time. I am having trouble creating a graph with time on the x-axis and count on the y-axis. Thank you in advance. You need to make an XY chart But with 30,000 data points you could run into trouble; Help says you can 32,000 data points in a single data series. However, if you want markers it will look like heck. You could limit the data like this: assume y-values in B2:B30001 in ...

VBA cell row formatting
I've been struggling with the code for this formatting issue. Any help would be greatly appreciated. If a cell (I10) on sheet A is selected as "ECD" then loop through all the other sheets and look in column E for cells that contain the text ECD. (It may be in a string, eg "No ECD") When ECD is found, I would like the entire row to be patterned with xlgrey25 (or some such). I understand that this should go in the worksheet change event of Sheet A. The trick is that in these other sheets the cell that contains ECD are actually merged of 3 rows.(I know, but I'm stuck...