Changing axis font with excel object embedded in VB6

[j2flk1234]

Hi,

I have a chart that I created in excel through VB. I want to try and change the font name from Times New Roman to Symbol. I tried recording the macro in excel but when I use the code in VB it doesn't work. Here is what I got:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
Dim xlWkb As Excel.Workbook
Set xlWkb = xlApp.Workbooks.Add
Dim xlSht As Excel.Worksheet
Set xlSht = xlWkb.Worksheets(1)
Dim xlChart As Excel.Chart
Dim MyShape As Shape
 
Set xlChart = xlWkb.Charts.Add
 
xlSht.Cells(8, 2) = 1
xlSht.Cells(9, 2) = 2
xlSht.Cells(10, 2) = 3
xlSht.Cells(11, 2) = 4
xlSht.Cells(12, 2) = 5
 
 
 
xlSht.Cells(8, 3) = 10
xlSht.Cells(9, 3) = 20
xlSht.Cells(10, 3) = 30
xlSht.Cells(11, 3) = 40
xlSht.Cells(12, 3) = 50
 
 
xlApp.Visible = True
 
xlChart.SetSourceData xlSht.Range("H10"), xlColumns
 xlChart.SeriesCollection.NewSeries
  xlChart.SeriesCollection(1).XValues = "=Sheet1!R8C2:R17C2"
  xlChart.SeriesCollection(1).Values = "=Sheet1!R8C3:R17C3"
   xlChart.Axes(xlValue, xlPrimary).HasTitle = True
  xlChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "p"
  xlChart.Axes(xlValue, xlPrimary).AxisTitle.AutoScaleFont = True
  xlChart.Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Symbol"
 
    xlChart.ChartArea.Select
 
    xlChart.ChartArea.Font.Size = 20
    xlChart.ChartArea.Font.Color = vbRed
 
    xlChart.ChartArea.Select
    xlChart.ChartArea.Copy
 
    Image1.Picture = Clipboard.GetData(vbCFBitmap)

The part in bold is where I try and change the font name.

Can anyone see what I'm doing wrong? This is really important so any help would be greatly appreciated.

Thanks

[Killer42]

Can you give a more specific description of the problem than "doesn't work"? Do you get an error? If so, what one?

[j2flk1234]

Can you give a more specific description of the problem than "doesn't work"? Do you get an error? If so, what one?

I don't get an error, nothing happens to the axis when I use the commands in bold.

[Killer42]

I don't get an error, nothing happens to the axis when I use the commands in bold.

Hm... but the rest of the code works?

[j2flk1234]

Hm... but the rest of the code works?

Yes everything works great. Thats what is really bothering me because all I need to do is change the font of the X and Y axis titles but it just doesn't change.

[Killer42]

Yes everything works great. Thats what is really bothering me because all I need to do is change the font of the X and Y axis titles but it just doesn't change.

Very odd.

I don't know what the problem is, but as a workaround, perhaps you could create a Font object, set it the way you want, then assign it to the .Font property of the axis. I've seen this done, but don't have any experience with it.

[j2flk1234]

Very odd.

I don't know what the problem is, but as a workaround, perhaps you could create a Font object, set it the way you want, then assign it to the .Font property of the axis. I've seen this done, but don't have any experience with it.

hmmm, not sure how to do it but I will look into it. Thanks!

[j2flk1234]

hmmm, not sure how to do it but I will look into it. Thanks!

I seemed to have figured it out. Near the bottom of my code I am setting some ChartArea properties and I guess these properties are overriding the AxisTitle properties. If I get rid of that code then the AxisTitle properties work.

[Killer42]

I seemed to have figured it out. Near the bottom of my code I am setting some ChartArea properties and I guess these properties are overriding the AxisTitle properties. If I get rid of that code then the AxisTitle properties work.

Cool! If it turns out you still need the ChartArea stuff, perhaps you can just change the order in which you set them or something.