Macro to automatically change chart line style based on cell text

Hi,

My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:

--------------------------------------------------------------

Sub Macro14()
'
' Macro14 Macro
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub


--------------------------------------------------------------

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?

Thanks,
Magpie

I have no experience with charts using VBA but maybe try:

Please Login or Register  to view this content.

but I give completely no guarantee that will work

Thanks for the reply, I tried that also but I get the same error (Run Time Error '13' Type mismatch)

Originally Posted by Sbarro79

Well, I expected that - this would be to easy
But I was really curious if it is possible to do, so I did some research and created a macro you may see in attached file. I know it's not perfect, but maybe you will find it helpful in some way.

Please note: you must change word "Wykres 1" to "chart1" (or whatever the default chart name is in your workbook) in code, because I use a polish version of Excel.

Thanks for the response, that works really well.

Can I ask where you found the research for this? I have been trying to do what you have done but make it so it applies to all the charts and series on the worksheet and for marker types, but haven't found anything that can help.

NM

Sorry for very late reply - I've been a bit busy recently.
I'm glad to hear you found it helpful. I understand that writing "research" was misleading but the truth is I only searched through some websites for how to change chart dash style using VBA and the rest of code is my own idea (created using trial and error or what if method). That's the whole secret