+ Reply to Thread
Results 1 to 3 of 3

VBA Chart Axis Formatting

Hybrid View

  1. #1
    Registered User
    Join Date
    03-10-2024
    Location
    Berlin
    MS-Off Ver
    mac2016
    Posts
    94

    VBA Chart Axis Formatting

    Hello everyone!

    I am trying to make use of Jon Peltier's "Chart Creation" code:
    https://peltiertech.com/Excel/Charts...tCreation.html

    I copied the CreateChart procedure into my "Sheet1" and I was able to set the different ranges.
    So far everything works, thank you, Jon!

    Unfortunately, I can't work out, how to format the Axis.
    Two things I'm trying to change:
    1. Font
    2. Show the vertical Axis without values and set a maximum value.

    This is Jon's code :
    Option Explicit
    '==============================================================
    Sub d1_CREATE_CHART_D()
    
      Dim objChart As ChartObject
      Dim myChtRange As Range
      Dim myDataRange As Range
      With ActiveSheet
        ' What range should chart cover
        Set myChtRange = Range("B7:C23") ' SET
        ' What range contains data for chart
        Set myDataRange = Range("B2:C3") ' SET
        ' Cover chart range with chart
        Set objChart = .ChartObjects.Add( _
            Left:=myChtRange.Left, Top:=myChtRange.Top, _
            Width:=myChtRange.Width, Height:=myChtRange.Height)
        ' Put all the right stuff in the chart
        With objChart.Chart
            .ChartArea.AutoScaleFont = False
            .ChartType = xlColumnClustered
            .SetSourceData Source:=myDataRange
            .SeriesCollection(1).Name = Range("B1") ' SET
            .HasLegend = False
            .HasTitle = False ' added by me, NO EFFECT?!
            .SetElement (msoElementChartTitleNone) ' added by me, NO EFFECT?!
            .SetElement (msoElementPrimaryValueAxisNone) ' added by me
            .SetElement (msoElementPrimaryValueGridLinesNone) ' added by me
            .HasAxis(xlCategory) = True ' added by me
    '            With .Axis(xlCategory).Select ' added by me
    '                Selection.Format.TextFrame2.TextRange.Font ' added by me
    '                    .BaselineOffset = 0 ' added by me
    '                    .Name = "Arial" ' added by me
    '           End With ' added by me
            With .Axes(xlCategory, xlPrimary)
                .HasTitle = True
                With .AxisTitle
                    .Characters.Text = "My X Axis"
                    .Font.Size = 10
                    .Font.Bold = True
                End With
       '             .ShowValue = True ' added by me
            End With
       '     With .Axes(xlValue, xlPrimary)
       '         .HasTitle = True
       '         With .AxisTitle
       '             .Characters.Text = "My Y Axis"
       '             .Font.Size = 10
       '             .Font.Bold = True
       '         End With
       '     End With
        End With
      End With
    End Sub
    '==============================================================
    And this is the Macro I recorded:
    Sub Macro_Axes_Font_Format_()
    
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).Select
        With ActiveChart.Axes(xlCategory).Select
            With Selection.Format.TextFrame2.TextRange.Font
                .BaselineOffset = 0
                .Name = "Times"
                .Size = 12
            End With
        End With
        
    End Sub
    The Macro causes an "Automation Error".

    Many thanks in advance for any help!

    Toni
    Attached Files Attached Files
    Last edited by briskie; 03-22-2024 at 12:25 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: VBA Chart Axis Formatting

    For category labels use

            With .Axes(xlCategory, xlPrimary)
                .TickLabels.Font.Name = "Times Roman"
    for vertical scale use
            With .Axes(xlValue, xlPrimary)
                .MaximumScale = 3000
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-10-2024
    Location
    Berlin
    MS-Off Ver
    mac2016
    Posts
    94

    Re: VBA Chart Axis Formatting

    Excellent Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-22-2015, 03:51 PM
  2. Axis Formatting on a Chart
    By Student1990 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-20-2013, 08:40 PM
  3. Excel 2007 : Formatting a chart axis
    By weiser_john in forum Excel General
    Replies: 2
    Last Post: 11-16-2011, 09:06 PM
  4. Formatting the y-axis of a chart
    By Sebastian1942 in forum Excel General
    Replies: 14
    Last Post: 09-14-2010, 12:44 AM
  5. Chart Axis Formatting
    By p135 in forum Excel General
    Replies: 6
    Last Post: 08-18-2010, 06:19 AM
  6. [SOLVED] chart axis formatting change
    By cporter@dallas.photronics.com in forum Excel General
    Replies: 2
    Last Post: 06-26-2006, 03:30 PM
  7. Formatting X-Axis in Chart
    By Scott in forum Excel General
    Replies: 1
    Last Post: 11-16-2005, 10:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1