Couple of Quick Questions ...

[BerylM]

Is it possible to reduce the space around chart labels in XL2007? We often work with quite small charts, and are finding that the minimum the space can be reduced to is horrendously large when you're using 4pt font in a chart that's only a couple of inches across!

And how do you reference the labels in an XL2007 chart? They seem to be listed separately from the series they refer to, so presume that has something to do with it ...

Also, could one use VBA to retrieve the location and name of the file that's currently No1 on the recently-opened list in Excel and Word 2007, or Visio XP? In the form that you'd use for a hyperlink, ie server/folders/filename?

Many thanks in advance for any help!

[HansV]

By chart labels, do you mean data labels? The DataLabel object doesn't have Width and Height properties, so you can't set its size...

The full path of the most recently opened file in Excel is

Application.RecentFiles(1).Path

In Word, however (long live consistency), this only returns the path of the most recently opened file; the name of the file is

Application.RecentFiles(1).Name

So you'd have to use

Application.RecentFiles(1).Path & "\" & Application.RecentFiles(1).Name

to get the full name.

[HansV]

I heard that you meant category labels. Unfortunately, the space around these is completely automatic; I don't think you can change it either manually or using VBA.

To set the number format and font size of the data labels of all data series in the selected chart:

Code: Select all

Sub FixDataLabels()
  Dim ser As Series
  For Each ser In ActiveChart.SeriesCollection
    If ser.HasDataLabels Then
      With ser.DataLabels
        .NumberFormat = "0.00"
        .AutoScaleFont = False
        .Font.Size = 6
        .Font.Bold = True
      End With
    End If
  Next ser
End Sub

[BerylM]

Thanks, Hans! What would we do without you!