Formatting the X-Axis on Multiple Graphs in the same worksheet with VBA: Debug Error - (.MajorUnitScale = xlMonth) RRS feed

  • Question

  • I am currently in the process of formatting multiple graphs in a worksheet into identical format. At this point i am running into issues with Debug disliking [ .MajorUnitScale = xlMonth ] line in the code shown below. My objective is to change the format of the graphs in the x axis to have a date format (e.g. July 14, 2014) and be able to specify the maximum and minimum values for the range. The code that i am using currently is show below. Also any suggestions on how to improve this overall would be appreciated.

    Dim cho As ChartObject
    Dim cht As Chart

    ' Delete the title of the graph is Opaware produces one

        For Each cho In ActiveSheet.ChartObjects
            Set cht = cho.Chart
            If cht.HasTitle Then
            End If
    ' Delete the chart X-axis title and rescale to the appropriately defined specificaitons

        With cht.Axes(xlCategory)
            .HasTitle = False
            .CategoryType = xlTimeScale
            .TickLabels.NumberFormat = "mmmm-dd-yy"
            .MajorUnitScale = xlMonth
            .BaseUnit = xlMonths
            .MaximumScale = "8/10/2014"
            .MinimumScale = "4/1/2014"
            End With

    'Remove the border around the legend

        cht.Legend.Format.Line.Visible = msoCFalse

    End Sub

    Friday, August 15, 2014 6:16 PM

All replies

  • Firstly should have an "s" on the end of xlMonth in the following line

    .MajorUnitScale = xlMonths

    The following 2 lines need to be actual dates (not strings) so use DateValue function to convert the strings to dates.

                .MaximumScale = DateValue("8/10/2014")
                .MinimumScale = DateValue("4/1/2014")

    Regards, OssieMac

    Saturday, August 16, 2014 11:31 AM