Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axes on chart in UserForm
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...chart/200808/1 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axes on chart in UserForm
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 _______ "Joe_Hunt via OfficeKB.com" <u45578@uwe wrote in message news:8901d05981229@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...chart/200808/1 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axes on chart in UserForm
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. Jon Peltier wrote: 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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axes on chart in UserForm
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 _______ "Joe_Hunt via OfficeKB.com" <u45578@uwe wrote in message news:8903473ed5b3a@uwe... 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. Jon Peltier wrote: 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 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axes on chart in UserForm
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. Jon Peltier wrote: 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] End If End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...chart/200808/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to swap axes on chart | Charts and Charting in Excel | |||
Bar chart with Y Axes Categories | Charts and Charting in Excel | |||
Having two X-axes on same chart | Excel Discussion (Misc queries) | |||
XY-Chart with 4 axes | Charts and Charting in Excel | |||
Is it possible to create 3 Y-axes in one chart? | Charts and Charting in Excel |