Axes on chart in UserForm

[Joe_Hunt via OfficeKB.com]

I'm trying, with limited success, to put labels on the charts on both the X
and Y Axes I have in a UserForm, and to make the option buttons do the right
thing. Everything I've tried to put the labels in has errored out. Also, My
boss would like to be able to see either the 2008 product chart or the 2009
chart or both. I put option buttons, and they work, except when you switch
from one to the other it doesn't clear the other one, just adds to it,
meaning there is now one of each. If you click back on the first one you get
three, then four, and so on (I haven't even tried to make it go side-by-side
yet. I'm confused enough already). Below is the code behind one of the option
buttons. I would greatly, greatly appreciate any assistance.

Private Sub OptionButton1_Click()
If OptionButton1.Enabled = True Then
Dim ChtSpc As OWC11.ChartSpace
Dim cht As OWC11.ChChart
Dim Sps As OWC11.Spreadsheet
Dim ws As Worksheet

Set ChtSpc = Me.ChartSpace1
Set Sps = Me.Spreadsheet1
Set ws = ThisWorkbook.ActiveSheet ' change to your worksheet name

Sps.Range("A1:AZ48") = ws.Range("A1:AZ48").Value ' Set worksheet range to
sheet control range
Set ChtSpc.DataSource = Sps ' set sheet control as chart control source
Set cht = ChtSpc.Charts.Add ' Add blank chart

' Set data for chart
With cht
.SetData chDimCategories, 0, "C19:C46" ' change to your category
range
.SeriesCollection(0).SetData chDimValues, 0, "E19:E46" ' change to
your series 1 range
.SeriesCollection.Add
.SeriesCollection(1).SetData chDimValues, 0, "T19:T46" ' change to
your series 2 range
.SeriesCollection.Add
.SeriesCollection(2).SetData chDimValues, 0, "AJ19:AJ46" ' change to
your series 3 range
.HasTitle = True
.Title.Caption = Sps.Range("B5") ' change to your title cell
.Type = chChartTypeLine
.HasLegend = True
.SeriesCollection(0).Caption = "ALG"
.SeriesCollection(1).Caption = "Pros"
.SeriesCollection(2).Caption = "Recommended"
.Legend.Position = chLegendPositionBottom
.Legend.Interior.SetOneColorGradient chGradientHorizontal,
chGradientVariantCenter, 0.5, "White"


End With

' hide the sheet control
Me.Spreadsheet1.Visible = False
' set the height of the chart control
Me.ChartSpace1.Height = 215
End If
End Sub

 

[Jon Peltier]

I don't know OWC's object model, but it's something like Excel's. You need
to access the axis titles through the axis. In Excel it's like:

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis Title"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis Title"

- Jon

 

[Joe_Hunt via OfficeKB.com]

I'd tried that before, but it didn't work. Here's the error message I get:

Compile error:
Wrong number of arguments or invalid property assignment.

Any ideas? I'll do a search in the "errors" section.

I don't know OWC's object model, but it's something like Excel's. You need
to access the axis titles through the axis. In Excel it's like:

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis Title"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis Title"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I'm trying, with limited success, to put labels on the charts on both the
X

[quoted text clipped - 63 lines]

End If
End Sub

 

[Jon Peltier]

Okay, I guess I'll try Google. Hmmm... OWC Axes.

The upshot is that there is only one argument for Axes. The argument 0
apparently stands for the bottom axis and the argument 1 for the left axis.
So try

.Axes(0).HasTitle = True
.Axes(0).AxisTitle.Characters.Text = "X Axis Title"
.Axes(1).HasTitle = True
.Axes(1).AxisTitle.Characters.Text = "Y Axis Title"

Also, you might have to use .Caption instead of .Characters.Text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I'd tried that before, but it didn't work. Here's the error message I get:

Compile error:
Wrong number of arguments or invalid property assignment.

Any ideas? I'll do a search in the "errors" section.

I don't know OWC's object model, but it's something like Excel's. You need
to access the axis titles through the axis. In Excel it's like:

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis
Title"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis Title"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I'm trying, with limited success, to put labels on the charts on both
the
X

[quoted text clipped - 63 lines]

End If
End Sub

 

[Joe_Hunt via OfficeKB.com]

I saw that right before I left work and forgot to respond. I sent myself an
email from home to put what I found out here in the morning. That solution
works. Thanks for looking.

Okay, I guess I'll try Google. Hmmm... OWC Axes.

The upshot is that there is only one argument for Axes. The argument 0
apparently stands for the bottom axis and the argument 1 for the left axis.
So try

.Axes(0).HasTitle = True
.Axes(0).AxisTitle.Characters.Text = "X Axis Title"
.Axes(1).HasTitle = True
.Axes(1).AxisTitle.Characters.Text = "Y Axis Title"

Also, you might have to use .Caption instead of .Characters.Text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I'd tried that before, but it didn't work. Here's the error message I get:

[quoted text clipped - 25 lines]