Trying to set colorindex of chart Error 438

[vba317]

After reviewing FAQ707-4811 and researching the issue I cant seem to be able to pick the correct object to change the color index and the border of my chart. I highlighted the offending code in Blue.Any help is appreciated.

Tom

Function AddChartSheet(strSheet As String, intTopDataRow As Integer, intBottomDataRow As Integer, intLastColumn As Integer, intPageCnt As Integer)
    Dim cht As ChartObject
    Dim rng As Range
    Dim intFirstChartRow As Integer
    Dim intLastChartColumn As Integer
    Dim intLastChartRow As Integer
    Dim strLocation As String
    Dim strDataSource As String
    Dim strSource2 As String
    Dim chtChart As Chart
    Dim strNameSource As String
    'Set location of chart
    intFirstChartRow = intBottomDataRow + 2
    intLastChartRow = intFirstChartRow + 19
    intLastChartColumn = intLastColumn - 1
    strDataSource = ConvColLet(intLastColumn) & intTopDataRow & ":" & ConvColLet(intLastColumn) & intBottomDataRow
    strLocation = "A" & intFirstChartRow & ":" & ConvColLet(intLastColumn) & intLastChartRow
    strNameSource = "A" & intTopDataRow & ":" & "A" & intBottomDataRow
             
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(WHERE:=xlLocationAsObject, Name:=strSheet)
   With chtChart
   'Set the chart type
    .ChartType = xlColumnClustered
    'Set the source data range for the chart.
    .SetSourceData Source:=Sheets(strSheet).Range(strDataSource), PlotBy:= _
        xlColumns
   End With
   With chtChart.Parent
      .Top = Range("A" & intFirstChartRow).Top
      .Width = Range(strLocation).Width
      .Height = Range(strLocation).Height
      .Left = Range("A1").Left
   End With
    'Remove Series Label
    ActiveChart.Legend.Select
    Selection.Delete
    'Add XAxisDataSeries
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = Worksheets(strSheet).Range(strNameSource)
    'Set up Font Structure for XValues
    ActiveChart.Axes(xlCategory).Select
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        If intPageCnt = 3 Then .Size = 9
    End With
    'ColorIndex definitions
     'Set up background color of chart
[Blue]    With Selection.Interior [/Blue]
    'Set Border color black and thin line
    .ColorIndex = 1
    .Background = xlAutomatic
    .Weight = xlThin
    .LineStyle = xlContinuous
    'Set background color to white on chart
    .ColorIndex = 2
    .PatternColorIndex = 1
    .Pattern = xlSolid
    End With
    'Reselect sheet so chart only is not printed
    goXL.ActiveSheet.Select ("A4")
End Function

 

[SkipVought]

hi,

After reviewing FAQ707-4811: Charts & VBA

where you found it in forum707...

I'd turn on your macro recorder and RECORD changing the desired property. Then observe the result.

forum707, please

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Thats where I got my original code

With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With

 

[SkipVought]

and...

did you???

If so...

WHERE IS IT???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

The code that I posted
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
is the code I got from the macro recorder

 

[SkipVought]

So you are using the [highlight]Category Axis Interior object[/highlight].

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...bottom line, the Category Axis has no INTERIOR object. You started out assuming the SAME SELECTION as was previously used.

BTW, I in as strong terms as I can muster, advise against using Select & Selection. Rather explicitly reference the object to apply property values. For instance in your code above...

    [s]ActiveChart.Axes(xlCategory).Select[/s]
    With chtChart.Axes(xlCategory).TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        If intPageCnt = 3 Then .Size = 9
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

OK, I am trying to understand the naming of objects. I am still getting the error 438 so I know I'm still doing it wrong. I tried plugging in your values and nothing worked.

 ActiveChart.Axes(xlCategory).Interior.Select
        With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
        End With

ActiveChart.Axes.Interior(xlCategory).Select
        With Selection

ActiveChart.Axes.Interior(xlCategory).Select
        With Selection

 

[SkipVought]

You either don't read what I write, do you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry, maybe it crossed on the wire.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You stated, " I cant seem to be able to pick the correct object to change the color index and the border of my chart."

So exactly what chart object do you want to change, when you say the BORDER? ChartArea? PlotArea?

What version Excel? In know that in 2007+ youo can actually SEE the name of the object in the Chart Tools > Format > Current Selection on the sheet

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I am running Access and excel 2003. I want to change the background plot area of the chart to white and the border area of the chart to Black. If I was doing it manually I would be using Format Plot area on the chart and changing the color on the border to black and the area color to white. I am sorry that our wires got mixed up. I do have a hard time understanding the different levels of objects in Excel and Access

Tom

 

[SkipVought]

I want to change the background plot area of the chart to white

    With chtChart
        With .ChartArea.Interior
            'Set background color to white on chart
            .ColorIndex = 2
            .PatternColorIndex = 1
            .Pattern = xlSolid
        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
Thank you for your help so far. I am not getting any errors but the code is not changing the chart. I don't know if this makes a difference but this code resides in a module in access.

Tom

 

[SkipVought]

What IS the ChartArea INTERIOR color BEFORE and AFTER? Please don't say white, white.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Both border and area properties are grey.

 

[SkipVought]

Are you saying that BEFORE they are grey and AFTER they are grey?

PLEASE help yourself and me!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I changed the colorindex number to 5 which is dark blue. The outside of the chart area changed to dark blue.

 

[SkipVought]

Well, THAT is NOT what you stated! "...[red]but the code is not changing the chart[/red]"

You DO realize that before you start programming something, you have to know WHAT you are doing? How proficient are you with Excel Charts? Right on the sheet? No VBA CODE? Do you know what the PlotArea is and how it really works? You need to, as it seem you do not. Try changing the PlotArea to NO FILL!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

The past two weeks is the first time I tried to use vba to program charts. I start with the microsoft website, then I search posts on this website, then I do google searches to try and get the answer. I respect this website too much to post anywhere else. I do apologize that when I first responded that the code did not change the chart I wasnt looking at the outside of the chart I was looking inside the chart. The color on the outside of the chart is white which I know now is why it didnt change.

I tried this and it worked

     'to change color background color to white
     With chtChart.PlotArea
        .Interior.ColorIndex = 0
     End With

Tom