?
Solved

How can I use VBA to format x-axis in chart to have one decimal place

Posted on 2011-10-21
10
Medium Priority
?
2,236 Views
Last Modified: 2012-05-12
I have pasted the code for a macro that I use currently for formatting data labels, etc.  I just want to add one last function to ensure that the x axis labels have one decimal place.

Many thanks
Sub FormatChartPoints4()
   Dim cht As Chart
   Dim n As Long
   Set cht = Charts("top half skyline chart")
   With cht.SeriesCollection(1)
      With .Points(1).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(2).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
       With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(3).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(4).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
         End With
         With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(5).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(6).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
         End With
         With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(7).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(8).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(9).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(10).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(11).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(12).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(13).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(255, 0, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(14).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(255, 0, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(15).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(16).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(17).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(255, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
      With .Points(18).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(255, 255, 0)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With
    .HasDataLabels = True
        .DataLabels.NumberFormat = "##.0"
        .DataLabels.Font.Size = 8
    End With
    End Sub

Open in new window

0
Comment
Question by:majervis
  • 5
  • 5
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37008463
The easiest way is probably to format your source data.
0
 

Author Comment

by:majervis
ID: 37008538
I should have mentioned that I have a chart that uses filtered data.  I format the chart, then change the filtered list and the formatting goes away.  The code attached reformats the chart with the correct chart point colors, label numbers, etc.  I just want to ensure that my x axis labels always show one decimal place.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37008559
You could it with code,

e.g Range("A1:A10").numberformat="0.0"
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:majervis
ID: 37008650
I trying to format the x-axis "active chart" number to show decimals.  I have not very familar with VBA...does a range name apply to charts.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37008662
What I mean is why not format your source data and then the chart labels will follow suit. Post a workbook perhaps?
0
 

Author Comment

by:majervis
ID: 37008843
seems like some code could just be added to what is in the attached to always force the x axis label to show one decimal place.  The above works on the chart once I run the macro.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37009027
Recording a macro suggests this will work:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = "#,##0.0"

Open in new window

0
 

Author Comment

by:majervis
ID: 37017908
I haven't had much luck using recorder for charting in MS office 2007.  I tried it and it didn't work.  Thank you anyways.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37017937
Posting a sample workbook would help.
0
 

Author Comment

by:majervis
ID: 37018010
I was able to figure it out with a little poking around.

Dim axs As Axis
Set axs = Charts("quintile chart").Axes(xlValue)
With axs
.TickLabels.NumberFormat = "0.0"

Thank you for your help.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question