Effective VBA Code for Conditional Charting

Hello there,

in a line chart, I need to show 3 different markers / colors
for data points depending on whether the basic data to be
shown are =, > or < zero.

My attempts to code this in vba works, but very slowly.

I suspect there is an effective way to do this, but can't
find it. Does anyboday know better ?

Thank you in advance,

Kind regards,

H.G. Lamy


0
hglamy (20)
12/4/2003 8:26:31 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
627 Views

Similar Articles

[PageSpeed] 59

Hi H,

Maybe you don't need VBA code at all.
Have a look at Jon Peltier's examples on conditional charts.
(http://www.geocities.com/jonpeltier/Excel/Charts/format.html#CondChart)

You should be able to modify the technique for lines.
Have one series for the line and 3 dummy series to display the 
appropriate marker for the data points.

hglamy wrote:
> Hello there,
> 
> in a line chart, I need to show 3 different markers / colors
> for data points depending on whether the basic data to be
> shown are =, > or < zero.
> 
> My attempts to code this in vba works, but very slowly.
> 
> I suspect there is an effective way to do this, but can't
> find it. Does anyboday know better ?
> 
> Thank you in advance,
> 
> Kind regards,
> 
> H.G. Lamy
> 
> 

-- 

Cheers
Andy

http://www.andypope.info

0
andy9699 (3616)
12/4/2003 9:24:56 AM
Thank you, Andy, that would indeed work.

Whether Jon's sample can be extended to show
different (customized) marker point as I intended
to do, I'm still trying to find out.

Kind regards,

H.G. Lamy


"Andy Pope" <andy@andypope.info> wrote in message
news:uskJ#ckuDHA.2340@TK2MSFTNGP12.phx.gbl...
> Hi H,
>
> Maybe you don't need VBA code at all.
> Have a look at Jon Peltier's examples on conditional charts.
> (http://www.geocities.com/jonpeltier/Excel/Charts/format.html#CondChart)
>
> You should be able to modify the technique for lines.
> Have one series for the line and 3 dummy series to display the
> appropriate marker for the data points.
>
> hglamy wrote:
> > Hello there,
> >
> > in a line chart, I need to show 3 different markers / colors
> > for data points depending on whether the basic data to be
> > shown are =, > or < zero.
> >
> > My attempts to code this in vba works, but very slowly.
> >
> > I suspect there is an effective way to do this, but can't
> > find it. Does anyboday know better ?
> >
> > Thank you in advance,
> >
> > Kind regards,
> >
> > H.G. Lamy
> >
> >
>
> --
>
> Cheers
> Andy
>
> http://www.andypope.info
>


0
hglamy (20)
12/4/2003 10:46:24 AM
Sure it can.  Each of the series in the Line chart example has its own 
markers.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

hglamy wrote:

> Thank you, Andy, that would indeed work.
> 
> Whether Jon's sample can be extended to show
> different (customized) marker point as I intended
> to do, I'm still trying to find out.
> 
> Kind regards,
> 
> H.G. Lamy
> 
> 
> "Andy Pope" <andy@andypope.info> wrote in message
> news:uskJ#ckuDHA.2340@TK2MSFTNGP12.phx.gbl...
> 
>>Hi H,
>>
>>Maybe you don't need VBA code at all.
>>Have a look at Jon Peltier's examples on conditional charts.
>>(http://www.geocities.com/jonpeltier/Excel/Charts/format.html#CondChart)
>>
>>You should be able to modify the technique for lines.
>>Have one series for the line and 3 dummy series to display the
>>appropriate marker for the data points.
>>
>>hglamy wrote:
>>
>>>Hello there,
>>>
>>>in a line chart, I need to show 3 different markers / colors
>>>for data points depending on whether the basic data to be
>>>shown are =, > or < zero.
>>>
>>>My attempts to code this in vba works, but very slowly.
>>>
>>>I suspect there is an effective way to do this, but can't
>>>find it. Does anyboday know better ?
>>>
>>>Thank you in advance,
>>>
>>>Kind regards,
>>>
>>>H.G. Lamy
>>>
>>>
>>
>>--
>>
>>Cheers
>>Andy
>>
>>http://www.andypope.info
>>
> 
> 
> 

0
12/4/2003 6:07:40 PM
Reply:

Similar Artilces:

Excel 2007 Chart in VB6 Program
I have another thread in the VB group and was told to post to these two groups so sorryfor the multi-post. Ill post there again just in case folks want to follow the thread. So I've had a Visual Basic 6 app (as an EXE) that has worked great for the last 9 years or so. Up until Excel 2003 I have had to make minimial changes for it to work properly. The app has an OLE control which acts as a container for many different external sources .In this control I embed an Excel workbook and display a chart sheet. I can interact with the workbook through the normal means of Excel programming objects...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Problem with Asset Allocator pie charts
Everything "seems" to be working fine with Money 2005 Premium (after restarting the program multiple times). I'm having a problem with many of the pie charts. They do not show up at all. I upgraded from Money 2004. Any ideas? I'm running Windows XP Professional if that's of any help. Thanks, Patrick I've seen them going on a new file on Win XP Pro - do you get them on a new file, or do they not appear on both a new and old one? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;...

Draw line in a chart
I have the following problem. I have a chart (date on x axis) on which I draw a line. How can I obtain the ending points of the line in order to compare, mathematically, their positions to corresponding values of chart at the same date? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements I'm sure your post makes perfect sense to you and might to other readers, but I am los...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

VBA Code to Open File1; Open File2 copy sheets to File1
Using XL 2003 & 97 Need help with the following VBA code: Sub Macro1() Dim myNewWorkbook As String myNewWorkbook = ActiveWorkbook.Name Workbooks.Open Filename:="OldWorkbookFile.xls" Sheets(Array("Formulas", "Department Lables")).Activate Sheets(Array("Formulas", "Department Lables")).Copy _ Before:=Workbooks(myNewWorkbook).Sheets(2) Windows("OldWorkbookFile.xls").Activate ActiveWorkbook.Close Windows(myNewWorkbook).Activate ' ........ End Sub My goal is to have myNewWorkbook active then:...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

VBA Combo Box
I have a combo box that I want to populate with a list of names. I do not want to reference names in the worksheet - using "RowSource" - I want to write the names in the code. I tried the following code: Private Sub ComboBox1_Change() With ComboBox1 ..RowSource = "" ..AddItem "Male" ..AddItem = "Female" End With End Sub This gives me an error. Does anyone know where the code should be added. Is this on a userform? If yes, then maybe it should be in the Userform_initialize procedure? If it's on a worksheet, maybe in the auto_open or workbook...

email charts
I have an excel workbook file containing many department sales charts that recieve data from a group of central data entry spreadsheets. Each department chart resides on its own sheet. Is there a way to email individual chart sheets (or snapshots of the chart sheet) to the respective departments without sending the entire parent file to all recipients? Thanks, Mnaut You could export the chart as a gif file, then email that file to the department. The following code creates a gif file from the active chart: '============================ Sub ExportChartGIF() ActiveChart.Export Filenam...

see chart from pushing button
how would I see a chart from pushing a button on the spreedsheet ? ...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Switching x-axis & y-axis on line chart
I am working with a large data spreadsheet with year, number and currency data. My problem is when I extract data to create a standard line chart, the chart is defaulting to a specific x-axis and y-axis format with data related to each axis. For visualization purposes, I would prefer to have the data that is currently presented on the x-axis to instead be presented on the y-axis. And likewise, I would like the y-axis data be presented in the x-axis. However, I have been unable to figure out how to do this and the MS Excel Help menu does not seem to address this. Can anyone help? If ...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Print dialog box using VBA in excel
Hi Everyone, I like to add a print button (that will select several worksheets and print them) in my excel worksheet. I have recorded my action using the macro recorder the problem is I can't select the printer everytime I run the macro it print in the background without asking the user to select the printer and uses the default printer. Can anyone help?? I just want the user to be able to select the desired printer every time before printing. My current code: Sub Print_All() Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select 'intent...

conditional running sum
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields: Date Adbn% 01/01/09 (Mon) 5% 01/02/09 (Tues) 6% 01/03/09 (Wed) 9% 01/04/09 (Thurs) 6% 01/05/09 (Fri) 8% I need it to end up with the following: Date Abdn% 01/01/09 5% 01/02/09 5.5% Avg of Mon&Tues 01/03/09 6.6% Avg Mon...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

Obtain Sub-Folder Names with VBA?
Is it possible to obtain the names of all Sub-Folders for a specific parent Folder? Just 2 levels (1 Parent Folder) and several children folders. We need the names of the sub-folders, not the names of the files in the sub-folders. Thanks for your help. Brad Lookup the Dir Function in the help file, specifically at the example it illustrates exactly how to get a directory listing. You'll see it isn't hard! -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the...

Excel 2003 - VBA
With code in Module 6, I created a button on a sheet as follows: ActiveSheet.Buttons.Add(1245, 16, 80, 16).Select Selection.Caption = "AutoFocus On" Selection.Name = "AutoFocusBut" Selection.OnAction = "FocusOn" Focus = True I placed the sub "FocusOn" also in Module 6. Public Sub FocusOn() If Focus = True Then .... .... When the button is depressed I get the message: "Ambigous name detected: FocusOn". What am I missing. Craig My bet is you have two 'things' named FocusOn. Two subs or a sub and a variable...

Stack Bar Charts
I have yearly information that I would like to graph in a stacked bar chart. I have years 2005 to present Two companies I am comparing with two variables for each company each year. I would like to have the stack bar chart by year reflect the stack of Co A vs Co B on variable 1 + 2. Any help would be appreciated. Thanks. You want a clustered-stacked chart: http://peltiertech.com/WordPress/clustered-stacked-column-charts/ http://peltiertech.com/WordPress/clustered-stacked-bar-charts/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sean wrote: > ...

personal income and expenditure chart
can anybody give me a personal income and expediture chart please Thanks Mike mikeyboy87 wrote: > can anybody give me a personal income and expediture chart please > Thanks Mike You can do this in Excel but why re-invent the wheel? Quicken or Money have far more tools and options. If money is an issue try checking ebay. I purchased Quicken 2004 for about $10 including shipping. It isn't the latest and greatest but it does what I need it to do. gls858 ...

Slow load for Analysis Toolpak
I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix? No, but I don't think you need the macro. Just select Tools, Add-ins, Analysis toolpak and it should load every time. "RyanK" wrote: > I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix? ...

Excel VBA
Hi All, I have a list that has been created by using the validation too (Data>Validation) Is it possible that when someone wants to choose something from th list they could say type the first few words and it would automaticall show. I.e you type "holi" or atleast part of the word and the cell shows th whole word. Thanks steve:confused -- Message posted from http://www.ExcelForum.com Data Validation doesn't include the autocomplete feature. You can type the entire entry, scroll through the dropdown list, or use keyboard (arrow keys, page up, page down) to navigate th...