VBA to select Line charts and format

[kpatel89]

Hello,

I have just really started to code on VBA but mainly from looking through such forums and using code from other macro's.

I have the following problem and feel i am just missing one simple piece of the puzzle as they say.

I need a macro when activated will do the following;

will look at the active sheet,

look at all the graphs,

select only the line graphs
(on the line graphs there might be 1 to 3 series, generally named after financial years e.g. 2012/13, 2013/14, 2014/15)

Will prompt the use to input the current financial year

from this information the macro will select the series the use has inputted in the step above.

Will then format this series ( i have the code to format to my specification)

Then ideally need the reversal so can turn the dashes back to connected lines.

So far the code i have is the following:</SPAN>

Sub New_year_LineChart()

 nCharts = ActiveSheet.ChartObjects.Count
   For iChart = 1 To nCharts
   ActiveSheet.ChartObjects(iChart).Activate   

   If ActiveChartObjects(iChart).ChartType = xline Then
               
        
    .Weight = xlMedium
    .LineStyle = xlNone
        
    .MarkerBackgroundColorIndex = 12
    .MarkerForegroundColorIndex = 12
    .MarkerStyle = xlDash
    .Smooth = False
    .MarkerSize = 7
    .Shadow = False
        
        
      End If
     ActiveWindow.Visible = False
    Windows("Test.xls").Activate

        Next
                      
End Sub

i think i am missing the link between activating all the chartobjects, making excel do an "IF chartype = xline, then function"..... format


Thanks for responses and sorry for my lack of skills

 

[NdNoviceHlp]

No answers should be a clue. What you ask for is not possible. If you want the user to select a particular chart and then have the ability to re-produce it with whatever formatting.... that would likely be within the realm of possibilties. Good luck. Dave

 

[kpatel89]

Thanks Dave I know it was a slightly ambitious

i have now adjusted the approach and now just have one sticking point so will be posting that as a separate post.</SPAN>