Difficulty customizing embedded chart in Userform

S

SQChevy

Board Regular
Joined
Sep 8, 2008
Messages
74
I have a section of code that controls a spreadsheet and chart that I am displaying data and I'm trying to customize the appearance of the chart. The usual commands to adjust a chart in excel don't seem to work. I will post my code below. I'm trying to set the range and make the scale logarithmic and adjust the colors of each seriescollection and also the location of the legend. I haven't included all of the code, but the others parts put the correct numbers where they are supposed to be. This is the only part that I am having trouble with. Please let me know if anyone knows how to do this:

Code: 
With DataAOF
'    .ScreenUpdating = False
    For I = 2 To 5
      .Cells(I, 2) = Q(I)
      .Cells(I, 3) = Pwssq(I) / 10 ^ 6
    Next I
    For I = 1 To 5
      .Cells(I + 1, 5) = Q(I)
      .Cells(I + 1, 6) = LSF(I) / 10 ^ 6
      .Cells(I + 1, 8) = Q(I)
      .Cells(I + 1, 9) = LSFsq(I) / 10 ^ 6
    Next I
    .Cells(1, 2) = "Measured"
    .Cells(1, 5) = "Least Squares Fit"
    .Cells(1, 8) = "Laminar Flow 1/n=1"
    .Cells(2, 8) = AOFc(2)
  End With
    
    
    With AOFChart
    
    .Charts.Add                                            'Makes a new chart
    .DataSource = DataAOF
    
    With .Charts(0)
      .Type = chChartTypeScatterLine
      .SeriesCollection.Add                                 'Put in a new series
      With .SeriesCollection(0)
        .SetData chDimSeriesNames, 0, "B1"                  'Series name is "Cole"
        .SetData chDimXValues, 0, "B2:B5"
        .SetData chDimYValues, 0, "C2:C5"
      End With
      
      .SeriesCollection.Add                                 'Make a new series
      With .SeriesCollection(1)
        .SetData chDimSeriesNames, 0, "E1"                  'Name new series "Least Squares Fit"
        .SetData chDimXValues, 0, "E2:E6"                   'Set x-values to xrange
        .SetData chDimYValues, 0, "F2:F6"                   'Set y-values to yrange
      End With
    
      .SeriesCollection.Add                                 'Make a new series
      With .SeriesCollection(2)
        .SetData chDimSeriesNames, 0, "H1"                  'Name new series "Least Squares Fit"
        .SetData chDimXValues, 0, "H2:H6"                   'Set x-values to xrange
        .SetData chDimYValues, 0, "G2:G6"                   'Set y-values to yrange
      End With
      .HasLegend = True
      .HasTitle = True                                      'Chart has a title
    End With
    
'    .ScreenUpdating = True
    End With
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Andrew,
I think from that code that the OP is using the Chart control from the Office Web Components library.
 
Upvote 0
I can't get any code that functions yet in VBA. That is what I'm asking for. Rorya, you are correct, I'm using Chart control from the Office Web Components library. If it were just in excel I would be able to use record a macro do to what I need, but unfortunately I can't do that here.
 
Upvote 0
Sorry I misunderstood.

Have you tried using the Object Browser on the OWC Control? For example I found the class WCScaling which has a property Type As ChartScaleTypeEnum, which can be chScaleTypeLinear (0) or chScaleTypeLogarithmic (1).
 
Upvote 0
I have tried doing that but I sometimes have trouble figuring out how to transmit that into code. So would I write it as:

.chart(0).WCScaling = chScaleTypeLogarithmic?
 
Upvote 0
I have figured out the coding for moving the legend to the bottom.

.Legend.Position = ChartLegendPositionEnum.chLegendPositionBottom
 
Upvote 0
I have figure out the coding for changing the scale, setting the range, and adjusting line color. I will post partial code below:

Code: 
With .Axes(0)
        .HasTitle = True
        .Title.Caption = "Pws^2 - Pwf^2 (x10^6 psi^2)"
        .Scaling.Type = chScaleTypeLogarithmic
        .Scaling.Minimum = 0.1
        .Scaling.Maximum = 10#
        .Font.Bold = True
      End With

      With .SeriesCollection(1)
        .SetData chDimSeriesNames, 0, "E1"         'Name new series "Least Squares Fit"
        .SetData chDimXValues, 0, "E2:E6"                   'Set x-values to xrange
        .SetData chDimYValues, 0, "F2:F6"                    'Set y-values to yrange
        .Line.Color = "Blue"
      End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA problem with setting data source range
Replies
1
Views
881
Akbarov
A
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
D
  • Question
Determine range for chart data using userform input - VBA
Replies
0
Views
387
dlee83
D
O
  • Solved
How to specify the chart title (caption) and y axis label or an embedded chart in VBA?
Replies
7
Views
573
Domenic
D
B
  • Question
how do i change the series names in my chart
Replies
1
Views
501
rlv01
R

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top