Specify style and color of marker in chart

Hello --

User plans to plot no more than 8 data series, and Wants series marker to be

- specified style("shape") (chosen from among 8 preferred shapes), and

- solid color (chosen from among 8 preferred colors)

Shape and color are arbitrarily assigned, depending on the sequence in which
the series is plotted.

Current code successfully plots the data, but does not assign marker style
or color the way we want.

Can someone tell me what is wrong with the following code?

Function SeriesPlot( _
        SeriesNum As Integer, _
        SeriesName As String, _
        RangeSeries As String, _
        RangeYVal As String)
  Dim srsSeriesNew As Series
  Set srsSeriesNew = ActiveChart.SeriesCollection.NewSeries
  With srsSeriesNew
    .Name = SeriesName
    .Values = Worksheets("Data_Series").Range(RangeYVal)
    .XValues = Worksheets("Data_Series").Range(RangeSeries)

'[a] the above 3 work fine; the following 2 don't give me what I want

    .MarkerStyle = GetMarkerStyleInt(SeriesNum)  'result is integer

    .MarkerForegroundColorIndex = GetMarkerColorIndx(SeriesNum)   'result is

    .MarkerBackgroundColorIndex = 15   'gray
  End With
End Function

Function GetMarkerStyleInt(SeriesNum) as Integer
       'returns integer related to SeriesNum
       'assignment is as follows
        Case 1
            GetMarkerStyleInt = 8          '"xlMarkerStyleCircle"
        Case 2
            GetMarkerStyleInt = 2          '"xlMarkerStyleDiamond"
        Case 3
            GetMarkerStyleInt = -4118      '"xlMarkerStyleDot"
        Case 4
            GetMarkerStyleInt = 9          '"xlMarkerStylePlus"
        Case 5
            GetMarkerStyleInt = 1          '"xlMarkerStyleSquare"
        Case 6
            GetMarkerStyleInt = 5          '"xlMarkerStyleStar"
        Case 7
            GetMarkerStyleInt = 3          '"xlMarkerStyleTriangle"
        Case 8
            GetMarkerStyleInt = -4168      '"xlMarkerStyleX"
End Function

Function GetMarkerColorIndx(SeriesNum)     'returns integer related to
    Dim aryColorIndexes
    'color indexes, in order of preference
    aryColorIndexes = Array( _
        1, _
        56, 55, 54, 53, 52, _
        49, _
        30, _
        25, 21, _
        18, 13, 11, 10, _
        9, 5, _
        12, 14, 16, _
        23, 29, _
        31, 32, _
        41, 47, 48, _
        50, 51, 3)
    GetMarkerColorIndx = aryColorIndexes(SeriesNum - 1)
End Function

Thanks in advance for any help.

Larry Mehl

mehl (38)
6/5/2004 11:50:30 PM
excel.charting 18370 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 2


Similar Artilces:

Attaching Chart to Email?
I have an Excel spreadsheet, and derived from it 5 charts. How can I attach one of the charts to an email to send to someone? (I know how to attach the whole spreadsheet, and the associated charts, but I don't want to send the spreadsheet and the other 4 charts, because they contain data not appropriate to share with the email recipient.) Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Hi CWLee Try this example http://www.rondebruin.nl/mai...

Copy Charts
I know this is simple, but I cannot remember how. I want to copy a chart into another worksheet, but do not want to keep the links, just the picture and data as it is. Select the chart, hold Shift while clicking on the Edit menu, choose Copy Picture, and use the On Screen and Picture options. Go to the place you want the chart image to appear, and use plain old Paste (Ctrl+V). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "archsmooth" <archsmooth@discussions.microsoft.com> ...

Background color on a tab page with XP theme
I have a dialog with a tab control on it. Tab pages are created from dialog resources. When the tab control displays, the tabs are "white" and the background of the tab pages is the COLOR_BTNFACE color. When I call EnableThemeDialogTexture for the dialog page object, now the background of the dialog appears to be "white" while the tab page has a white gradient getting darker toward the bottom. This leaves an outline around where my dialog resource is being displayed. How do I get the gradient to come out right? Do I need to call DrawThemeBackground on the page?...

bar chart prints 1 unwanted horizontal row of dots & tick marks?
Office Excel 2007. Anyone encounter this? The sheet I'm printing is 19 columns wide by 69 rows high with portrait orientation. Bar chart is set between row 25 and 47 horizontally. The x-axis is increasing months. 2 vertical bars are set between the major gridlines. It so happens that the chart vertical gridlines are very close to lining up with the spreadsheet cells above/below the chart. Print preview shows nothing unusual, but when printed, 1 row right throught the middle of the chart has 1 to 2 dots and a character high tick mark appear to the right of each right most(second) bar...

Stacked Bar Chart Over 100%
Currently have a stacked bar chart showing to 100% (the plan) by 8 clients, how can I show that 3 of the 8 will be over 100% (greater than plan)? Brian, If this is a normalized chart (the one on the right in the chart types), it will always be 100%. You'll have to use the middle one. Are these percentages (as they appear in the worksheet)? If so, use the regular stacked chart (the middle one), and the axis should adjust to accomodate whatever your greatest percentage is. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brian" <Bri...

Save chart (that's on a chart sheet) to html file?
In Excel 2007, is it no longer possible to publish a chart that is on object in a worksheet to an html file? In Excel 2003, you had the option to choose a chart as the item to publish. That no longer seems to be available. ...

Aligning tick marks and labels on line charts or scatter charts
I have the following data: X Y 1 0.2 2 0.8 3 0.5 4 0.7 I would like to plot it using a line chart or a scatter chart with X on the horizontal axis and Y on the vertical. I would like the X range of the graph to go from 0.5 to 4.5, so that the first and last point are NOT on the extreme edges of the graph. I would like the X axis to be labelled only at 1, 2, 3 and 4. I would like a tick mark aligned with each of these labels. Is this possible in Excel? Here is what I am finding... Using a Line chart: Option 1. The range is correct (0.5 to 4.5), and the labels are correct, but the tick marks ...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: http://www.peltiertech.com/Excel/ChartsHowTo/index.html When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

VB Chart > Excel
I have a vb program that pulls data from a database and displays it onscreen. This includes a chart - is there a quick way of bascially copying this chart into an excel spreadsheet via code from my program? I have already got my listboxes and so on to fill in their info onto a new blank sheet, but the chart is also going to be handy. I assume one way is to copy all the data from the chart to a hidden sheet and use that maybe, but I am hoping I can do something simpler... Ta, James. James - Make a template that has some dummy data and the chart you like, already formatted. Create a new fil...

ow to create comparison chart using text as value data?
lplease help me. I am trying to set up a comparison chart of 3 computers cost and features! I cannot figure out how to use the features as values on the x or y axis says data range is to complex so cant select rows or columns. I am trying to enter the features as my source data too bungled dont know what to do term project is now overdue and I am stressing out!~ Dont want to fail. please help me!do i have to redo the whole chart or what any suggestions and demonstration would be deeply appreciated. thanks, Dee Johnson The first thing you need to do is think how the chart should look. Wh...

regarding allocation of chart series point colors
Greetings and thanks for your time I have labels A,B,C,D and data series of values a%, b%, c%, d%. This series is sorted into ascending order of values and feeds a bar chart (ie a Pareto chart). I would like to display this data also on a Pie chart, but would like to keep the same series point color for A,B etc. regardless of how the updated values change the rank order (say, C,B,A,D). Presently, if say, point A is dark blue at the start of the order and then after updating the % scores point A moves down to 3rd in the order, point A will take on another color. Any advice will be appreci...

How to see styles of paragraphs in a pane on the left?
In Word 2003 I could get three panes up when editing a document: - the text itself in the middle - a narrow pane on the left showing against each paragraph the style it's in - a pane on the right for selecting styles and for changing them In Word 2007 I can get a Styles pane up on the right of the text, but I can't see a way to get its paragraph styles shown in a pane on the left. Can it still be done? -- Seamus O'Connell Seamus O'Connell wrote: > In Word 2003 I could get three panes up when editing a document: > - the text itself in the middle > - a...

Has anyone found a way to search for styles and replace them with other ones? Surely Publisher 2003 must let you do this? ...


automatically adjust charts
I am always charting 2 years of data. I have 2 spread sheets, one has multi charts and the other is all the data for the charts. I'm tracking about 6 different data points in a line chart. Each month the data sheet gets the previous months production data. I'd like to have it automatically adjust the charts. Drop the oldest month and add the newest month to the chart. That would save me the time of manually updating the charts. -- Baltimore Ravens Ravens Fan: You can make things a lot easier for yourself by creating a dynamic chart that plots the 24 months of data, or whateve...

Best Line chart to use?
I'm trying to create a chart that shows the run time (in minutes) o data backups at work. They start at various times from 00:00 to 23:59, with varying lengths. 3-D looks the best on paper, where it seems best to have the 24 hou clock across the x-axis with the run time as the Z-axis. I then need t somehow have each server running 'into' the graph for the run time This allows me to have a few 'stacked' on top of each other in cas multiple servers all start to backup at say, 03:00. Only I can't get it to work..lol Can you suggest a more relevant chart type, or if not...

I want to show chart data in order of bars, not row names
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to show a chart that has about 20 values, and I have sorted the source data so that I have the highest value items at the top and the lowest value items at the bottom. For example, if rows were Apple, Banana, Guava, Orange, and Yucca, I have sorted the data in order of the greatest value items (20 Guavas, 10 Oranges, 5 yucca, 2 bananas, and 1 apple). Yet the chart (a bar chart) only seems to let me display in either ascending or descending value by food name (either Apple to Yucca or Yucca to Apple...

Chart Zoom and Scrolling
I writing an application that will allow a user to zoom in on a chart page. However, the zoom function defaults to upper left edge of chart. How can I cause the initial view to be based on the upper right edge of chart. During zoom-in the scroll bars appear but I don't know how to move them in VBA assuming that is the way I should tackle this problem ...

How can I change the color in a eps file?
I can get the file inserted but cannot change the color. Any suggestion? Thanks Debby You can change the .eps file to one color only if it has a .tiff header, right-click the image, format picture, picture tab, recolor. If the .eps file has a .wmf header, select the picture, arrange, ungroup. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Debmck" <Debmck@discussions.microsoft.com> wrote in message news:2367DFCF-0391-4BD8-87D8-466B46F1E8AA@microsoft.com... >I can get the file inserted but cannot change the color...

Chart bug? Excel XP
I've been running into a situation that continues to crash my Excel. I'm wondering if anyone else has encountered this. I often create text boxes that link to a cell in the worksheet to pull in a special message or label into the chart. I do this by clicking on the formula bar, typing "=" and then using the mouse to navigate to the cell. (Typing the cell reference doesn't seem to work.) I have found that if the cell I am referring to is a one-cell, named range, Excel crashes. In this case, I'm better off navigating to a near-by, unnamed cell and then editing the ...

Style option lost
I used to be able to highlight a table and have the ability to choose a style theme/color scheme for it (Excel 2007). The Table Tools|Design option would automatically appear. It no longer does. Anyone know how I can get it back; or do I have to uninstall Excel and reinstall it to get things right again? I have checked everything I know and can't seem to retrieve it. Thanks in advance, Bob Sounds like the table lost its tableness. Select all of the table again and on the Insert tab click Table. -- Jim "Bob Vanderslice" <rvandy1613@cox.net> wrote in message...

Rotating a Text Box in a Chart
Is there a way to rotate general text on a chart? There does not appear to be a way to rotate a text box. Thanks, Paul If it is in a cell then you should be able to right click on cell and goto format cell then click on alignment tab and you can rotate upto 90 degrees up or down. >-----Original Message----- >Is there a way to rotate general text on a chart? There >does not appear to be a way to rotate a text box. > >Thanks, >Paul >. > There is some basic capability. Double-click the textbox, and in the resulting Format Text Box dialog box, click the Alignment...

XML generating excel charts
Hi, I have an ASP webapplication that exports a dataset to an excel workbook via XML. My question is if it would be possible to write XML-code that automatically draws charts in Excel based on the data exported. Let's say I have one DataTable with 2 columns. Then I want to export the raw data to sheet1 in excel while sheet2 plots the data from sheet1 only using the supplied XML code. What you want to do is to put the data into a workbook that has a template with a chart already setup and posibly has a macro already built into the workbook. XML data is really ascii...

Color coding an excess point
I have a very large excel file. When looking at a particular cell for editing sometimes it's difficult to see that I'm in the correct row and column. Is there a way to make the active cell (column and row) accented by color when editing it? Thanks! Kat Here is a post from earlier Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlCont...

error 286: the other application in the DDE conversation isn't responding in the time specified by the OLE/DDE timeout setting
I have a program in Access 2003. I use DDE commands for sending data to excel and setting up the page setup. on 2 machines the error "error 286: the other application in the DDE conversation isn't responding in the time specified by the OLE/DDE timeout setting" appears. No error appears in Excel. Any help ? ...