Define a chart using VBA

Hi folks,
I'm baffled-  I'm writing a macro to pull a data table into an excel
sheet & create a bargraph of those results.  The number of records in
the data table are variable (say between 2 and 50).  

I'm trying to define a dynamic bar graph using VBA.  The X values are
in col C and the text Y vlaues are in col A. (both start in row 3) The
values in column B are necessary, but have nothing to do with the
graph.  I'm having trouble using variables to define the source data as
two, non adjacent ranges. 

I've had problems just including the 50 cells in the data table as it
includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cell
C3.  It just numbers them on the Y axis, not by the corresponding text

Any ideas?

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A"))  
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
..Overlap = 0
..GapWidth = 140
..HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
..Overlap = 0
..GapWidth = 140
..HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
..CrossesAt = 1
..TickLabelSpacing = 1
..TickMarkSpacing = 1
..AxisBetweenCategories = True
..ReversePlotOrder = True
..MajorTickMark = xlOutside
..MinorTickMark = xlNone
..TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
..TickLabelPosition = xlHigh
End With
With Selection.Border
..ColorIndex = 16
..Weight = xlThin
..LineStyle = xlContinuous
End With

Pim's Profile:
View this thread:

9/26/2005 11:48:53 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 52

i got it- thanks guys!

Pim's Profile:
View this thread:

9/27/2005 9:57:04 PM

Similar Artilces:

Another odd VBA glitch in 2007
With the following code 2007 will change the colours but won't change the border lines In fact it takes out all the border lines that were already there. Anyone know a workaround? ActiveChart.SeriesCollection(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 38 .Pattern = xlSolid No workaround to offer, but we can give all of these a name? YACAGG: Yet Another Carting and Graphic Glitch ?? They seem to be piling up. "teepee" wrote:...

External Tasks and useful BI Reporting
Let's say I have a single schedule that lists release milestones of all my in-flight software development projects. Then, all of my implementation project schedules that are going to implement that software use a cross-project dependency to make it clear that the implementation is awaiting on the completion of the particular software product in development. This much we do currently. But what is the best way to get useful BI reporting off that master schedule of milestones? For instance, I want to see a listing of "all projects that are depending on the release date of ...

Tooltip for each point in Scatter Chart
Hi I have plotted a Scatter chart that uses three series. I cannot use datalabels for identification of each topic, as when points are very close, it gets very messy. The "tip" (I donno what its called) that comes on mouse hovering is not of much help as it simply specifies the series and each point, which in my case is useless I want to use the mousemove event( I donno how correct I am ) to display the tip , picking the data for each point that is gathered along the data on which the chart is plotted Any lead is appreciate TI Shilps Check out the Excel/Add-Ins/Hover Chart Label pa...

Defining an email archive strategy
I need to put together a plan that will allow users to access old mail, yet still keep our server storage under control. Our business rule will likely be really simple (so people get it) that mail stays on the system for 60 months... period. Ideally, this plan would include the creation of .pst files that could be accessed by end users as a way to do service retrievals of old mail. Are there any resources/white papers on segested configurations of auto-archive used in conjunction with a a particular backup/restore configuration? Thanks, Steve ...

How to show 5 data series on one chart
Hello, Here are the data that I have: - a category that will be my X axis - a percentage that will be my Y axis. I have a percentage value per category per year. - 3 company names - 2 years (2004 and 2009) What I want to show is the following: For each company, I would like to plot a solid colored line representing the different % for each category for 2004 and a dotted colored (same) line representing different % for each category for 2009. As a result I would get three sets of lines, three different colors, and within each set a solid and a dotted line. I currently have my table orga...

Support wide char using BYTE*
Hello therer I am using a DLL whose function supports only LPSTR and does not have wide char support. I can't modify it since it is a third party DLL and in any case I want to support wide char with same DLL. What would be the workaround ? Let me know your views. One solution could be to convert the LPWSTR into BYTE* and pass it to functions of DLL to store as LPSTR. Is it correct ? If yes, Let me know how to convert LPSTR to BYTE*. Regards Ajay Sonawane If it expects one, if you pass it the other, by any means, it will balls up in an absolutely huge way. -- - Mark Randall http://ze...

Compact OE
Several people I know who do not use Outlook Express keep getting messages when they boot their computer to Compact Outlook Express messages. They don't do it, not knowing what the message is for or about. How can they stop this message from appearing since they don't useOE? Mich After XP SP3 install repeated prompts to compact Outlook Express (OE). Starting with XP SP2, you get the prompt to compact after 100 closings of OE and every one thereafter until you compact. SP3 changes the way the compact count gets incremented. With SP2, only closing the main program...

Repeating colors in excel 2007 area charts
I'm in the process of converting dynamically generated excel charts from Excel 2003 to 2007. A couple problems I have resolved, but so far have not been able to find a solution to a color repetition problem. The charts can have anywhere from 2-3 legend entries to 15 -20. Problem is after the 1st (lowest) three appear as individual colors, all the colors after that are the same color. When this same chart is generated in 2003 no problems, 2007 repeating colors. So far I've tired to find some sort of legendkeys command to set the colors to xlautomatic (which also works fine ...

Use Outlook Client with OWA
My company provides email access via OWA. Is it possible to use the normal Outlook Client to access this email account?. Submitted using You should ask the company you work for if they support RPC over HTTP (a.k.a. Outlook Anywhere). If they say yes and you are running Windows XP with SP2 or newer with Outlook 2003 or newer, then your in like Flint. "john" <jrubis[at]ptd[dot]net> wrote in message news:uaUAqiq7KHA.5820@TK2MSFTNGP04.phx.gbl... > My company provides email access via OWA. Is it possible to use the normal > Outlook...

files required to use windows update are no longer registered or i
Here is some history.... For some reason, the SP3 update would never finish installing on my system. After may hours of investigation and trial/error, I ran across a posting the suggested the following 1. Download and install subinact.exe. 2. Open Notepad (Start Menu-All Programs-Accessories-Notepad) and copy and paste the text below into the new Notepad document: subinacl /subkeyreg HKEY_LOCAL_MACHINE /grant=administrators=f /grant=system=f subinacl /subkeyreg HKEY_CURRENT_USER /grant=administrators=f /grant=system=f subinacl /subkeyreg HKEY_CLASSES_ROOT /grant=administrat...

Using Find in an Exchange Environment
Hello Everybody! A user recently complained that he could only search through his Inbox to find emails that were sent August 2005 and before. Mail that was sent last week couldn't be found. I thought this this might have to do with the full text index that I have on that particular store. Unfortunatly this doesn't not seem to be the case. Any ideas would definitly help out big time! Dust On 18 Jul 2006 13:25:09 -0700, "Dust" <> wrote: >Hello Everybody! > >A user recently complained that he could only search through his Inbox >to fi...

using offset with charting
how can I use the offset function to select the starting row and ending row of a range I wish to chart. I have 100 rows but I wish to select any 10 consecutive rows to chart, by, say, typing 7 into cell A1, so that the chart displays rows 7 to 16. thanks Hi Bob, From a very recent post on the same subject. Jon Peltier has a collection of examples, As does Tushar Mehta, Cheers Andy bob farey wrote: > how can I use the offset function to select the starting &g...

E-Mail Templates for Campaigns & using "slugs" directly in e-mails
Hi Is there any way to use e-mail templates for campaigns? When distributing campaign activity via e-mail it is not possible to select a template to use. one of the main reason I would like to use template is because of the "data fields" (which is a must in my case) you can use in the templates. And also, if it's not possible to use templates for e-mails in campaigns - can one possibly use the data fields or "slugs" as they are sometimes called directly in the campaign e-mail? I have tried without any luck ( for example: {!Account.Account Number;} ) Hi Einar, u...

Excel VBA #6
Hello, I have created a form using vba in excel to input data into the database. I have also managed to delete, using the multi-list form to select and delete a database. Using VBA: Is there a way where i can create a form to search that have a criteria, for eg. i type "H" to find names starting with "H". AND Is there a way i can create a form where i can EDIT the data that is already in the database. THANK YOU SOOO MUCH. Sure. You can loop through the database range and look for your criteria. Then if you find it, you can add it to the listbox. dim myCell as ran...

VBA coding a nested Vlookup, and a sumif formula
I need help. I have this formula: =3DIF(ISERROR(VLOOKUP(IF(C3=3D"",VLOOKUP(D3,Data!B:O,F3+2,0),IF (D3=3D"",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O $114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF(C3=3D"",VLOOKUP(D3,Data! B:O,F3+2,0),IF(D3=3D"",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0))) And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for worksheets, but it gives me #Value! at row 100. Sub Run_Data() Dim iLastRow As Long Dim i As Long With Sheets("FBL3N_1&quo...

Adjusting the Y axis of a chart
I have created a line chart that have three different number range associated with it. Two of the three groups are close to each othe (the numbers are between 5000 and 8000), but the third group i significantly higher (between 180000 and 230000). So my graph barel shows the first two groups at the bottom of the chart. I would like t take out of the graph the range 25,000 to 175,000 to show more of th detail for the first two groups. Can this be done? Thanks, Keit -- Message posted from Hi Keith, Jon Peltier has a page describing this problem, http://peltier...

Advanced chart question
Hi, I have an interesting question on charting, hope someone can help me out with it. For a performance analysis I am showing both the performance of a portfolio (line) and its corresponding benchmark (bar). In order to make the out- or under performance more visible I would like to give to "balls" in the line a red color in case of under performance and a green color in case of out performance. Obviously you can do this manual but I have over twenty graphs with 20 points;- (........... Any suggestions if this can be set automatic?? many thanks! Rgds Robert Hi See if the arti...

Using profiles to auto configure a users email settings
Hi All, Firstly appologies for this old question but I have been trying to get this working for a week now and just keep going around in circles. We have network of 2000 odd users sharing 400 pc's on the 2k boxes anyone can log in but if they have not signed in before they are required to configure their outlook profile. I have played with default.prf and the modprof.exe file usung the-S -Z. All appears to work it says the profile is created etc. when the user goes into outlook it does not grab their username from the default.prf file... Here is the top part of this file : [General] Cu...

Outlook XP crashes when using Tasks
Outlook will crash when using Tasks. Here are the things I have tried. Used XP's Restore function to go back to a time when it worked fine. 1. Ran 2 virus scans with two different products. 2. Deinstalled Office XP, ran a defrag, and then reinstalled. 3. It will function propertly for about 2 minutes and then crash. There is an Exchange 2000 server involved as well. He can access his Tasks on another PC with no problem, so I know it is related to his machine. The offending file is an Outllib.dll, at least from the technical notes and log files. Any thoughts? ...

Using EAN barcodes
I am using SO 2.0 SP1. I need to setup some products with EAN13 barcode format. We are contemplating to use GS1 registered barcodes. I was wondering if anybody is using EAN barcodes and if there are any "gotchas" I should look out for. I would probably use a 12-digit product number, which would be good enough for our purpose. Or is there any advantage to use a 13-digit number? I also tried to print some EAN13 labels but did not find a template that prints the item code right underneath the barcode, kind of embedded in the barcode. Is that a special template that need to be us...

need variable time axis available in XY chart for area chart #2
I am unable to use different X axis in the Area chart, which is available in the X Y charts. How do I use the X Y chart features of variable X axis to make the area chart? ...

Need "IsModified" using CPropertySheet, CPropertyPage, SetModified
I have a Property Sheet with several Property pages. I have need of knowing if any page has been modified (any page's "IsDirty" flag is set). I know the "Apply" button is enabled if any page is dirty, but I haven't found any way to ask the sheet or pages if they are "Modified". I'm sure this is simply -- I just haven't found it. Does anyone know how to determine if a page is dirty, or better if the Sheet is dirty? Thanks DanB One quick way that comes to mind is just to do an IsWindowEnabled() check on the Apply button... :o) I think y...

Chart with colored weekends
Is it possible to have a date chart with a gray bar marking Saturdays, Sundays and declared Holydays so I can Gantt my deliveries? Just a few tips on how to get there... PS: by the way Jon, I've been learning A LOT from your examples. Thx for your excellent pages. ...

Formatting dates on x axis of chart...!
Win XP HE Excel 2002 Hi, I have a chart with a simple y and x axis. The x axis represents dates. In sheet1 of the spreadsheet being used, the dates are in column A and in each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the dates are in this format: 12/20/2004.) The result is (obviously) the same number and format 12/20/2004 in sheet1. When I chart, for the life of me I cannot get rid of this format in the x axis labels--I am usually able to define the label format in tha chart itself but, nothing I do lets me change this format. Even when unchecking the "link to sou...

Use Macro to Select printer in Word 2007 on Windows 7 Pro
I am trying to migrate the Marco's I use for my Word 2007 users. The most popular feature is to select the correct printer and send labels & Envelopes. I have a new 64bit Windows 7 Professional machine I am trying to configure. The printer(s) are installed as follows: Local Printer using a TCPIP address port to a HP 4240n printer using the HP Universal Windows 7 Driver. The printer was installed as B108-Labels. It appears in the printer list My vbScript currently selects the printer from my Print Server "\\printers\b108-labels". I changed the script...