Free Floating a Chart

brgipple · Mar 15th 2006

Hello everyone
I have question on how to get a chart to free float on my page. i have the following code and i added a section to try and do this but i can't seem to get it to work. any idea's?

Code

Charts.Add
            ActiveChart.ChartType = xlXYScatter
            ActiveChart.SetSourceData Source:=Sheets("Neg Bump").Range("D2:E" & NumOfRows), PlotBy:=xlColumns
            ActiveChart.Location Where:=xlLocationAsObject, Name:="Neg Bump"
            ActiveChart.Axes(xlValue).Select
            With Selection
                .TickLabelPosition = xlHigh
            End With
                Selection.TickLabels.AutoScaleFont = True
            With Selection.TickLabels.Font
                .ColorIndex = 41
            End With
                ActiveChart.Axes(xlCategory).Select
            With Selection
                .TickLabelPosition = xlHigh
            End With
                Selection.TickLabels.AutoScaleFont = True
            With Selection.TickLabels.Font
                .ColorIndex = 41
            End With
            With ActiveChart
                .HasTitle = True
                .HasLegend = False
                .ChartTitle.Characters.Text = "Negative Bump"
                .Axes(xlCategory, xlPrimary).HasTitle = True
                .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Neg Bump Raised to Power"
                .Axes(xlValue, xlPrimary).HasTitle = True
                .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Delta Pitch"
            End With
            ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=True).Select
            ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select
            'With ActiveChart.ChartObjects
            '    .Placement = xlFreeFloating
            '    .PrintObject = True
            'End With
            ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
                Selection.Left = 50
                Selection.Top = 270
            ActiveChart.SeriesCollection(1).Trendlines(2).DataLabel.Select
                Selection.Left = 330
                Selection.Top = 270

Display More

Dave Hawley · Mar 15th 2006

Re: Free Floating a Chart

Hi brgipple

I would simply embed the chart on the Worksheet and use Freeze Panes or Window Split

**Andy Pope** · Mar 15th 2006

Re: Free Floating a Chart

Hi,

To get the code to work try this mod[vba] With ActiveChart.Parent
.Placement = xlFreeFloating
.PrintObject = True
End With[/vba]This just means the chart will not be effected by row/column resizing or deletion.

**Andy Pope** · Mar 19th 2006

Re: Free Floating a Chart

Brian asked me these follow-up questions

Quote

Andy i have another question about charting.

I have the following code but when i add the chart it gives me 2 series od data on my chart instead of just one. is it because the columns are not next to each other?

If PitchNominal <> vbCancel Then
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Neg Bump").Range("D2:D" & NumOfRows, "F2:F" & NumOfRows), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Neg Bump"
Also i have added alot of charts when i was making my code and spreadsheet. i have been deleteing them or cutting them off the sheets. but when i recoreded a macro last night it showed the new chart # at 102, do i have 101 invisible charts in my spreadsheet, if so can i get rid of them or reset this counter.

Thanks again

Brian

Display More

Subtle change so the comma is part of the range.
[vba] ActiveChart.SetSourceData Source:=Sheets("Neg Bump").Range("D2:D" & NumOfRows & ",F2:F" & NumOfRows), PlotBy:=xlColumns[/vba]
Use this code in the immediate window (CTRL+G) to see exactly how many chart objects you have[vba]?activesheet.chartobjects.count[/vba]The shape index value does not get reset so the actually number of charts is not reflected in the index number.

brgipple · Mar 19th 2006

Re: Free Floating a Chart

Andy

The code change you gave me worked for my second series of data showing up in my chart, Thanx

I tried the code you gave to use in the immediate window (CTRL+G) and i got a alarm "reference is not valid", not sure what that means in this window.

Also on my chart i want the data labels for my trend lines to stay where i put them, they will until i start to manually change a input to the chart, is there a way to float or anchor the datalables where i put them. I tried the following code, but alas it did not work. When I looked at the formatable items for a DataLabels i didn't see anything for doing this like you can for a chart, is it possible to do?

Code

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
            Selection.Left = 15
            Selection.Top = 285
    With ActiveChart.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
            .Placement = xlFreeFloating
    End With

Thanx for your time and effort

Brain G.

**Andy Pope** · Mar 19th 2006

Re: Free Floating a Chart

When using the immediate window did you have a workbook open?

Data labels do not have a Placement property like shape. Instead they have a Position property.
Trendlines do not have datalabels.

What exactly are you trying to do and can you post and example of your chart?

Dave Hawley · Mar 19th 2006

Re: Free Floating a Chart

Hi Brian

Please keep all correspondence in the public forum so as to avoid confusion.

Thanks Andy for posting the Q&A and for answering it!

brgipple · Mar 20th 2006

Re: Free Floating a Chart

Andy

Sorry, I miss spoke earlier about the trendline datalabels, I ment to say the trendline equations that you can show on the chart. After I load all my data into the Sheet, set my formulas, create my chart and then put my Equations where i want them on the chart, if I manually change some of the data the Equations will move all over my chart. Is there a way to anchor the equations to certain spot on the chart?

I was going to attach a sheet out of my WorkBook so you could see what was happening, but the size is bigger than 48k (around 2M). can I send this to you? And if so how do you want it sent?

As for the Immediate Window, i had the Workbook open that would have had all the charts. and i got the message that showed earlier.

Brian G.

**Andy Pope** · Mar 21st 2006

Re: Free Floating a Chart

Hi,

The data labels for the trendline are moving relative to the position you put them in and the end of the trendline.
You will need to reset their position using code. Add the following to the sheet object to position the labels bottom left/right.[vba]Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("H2")) Is Nothing Then

With ActiveSheet.ChartObjects(1).Chart
.SeriesCollection(1).Trendlines(1).DataLabel.Top = .ChartArea.Height
.SeriesCollection(1).Trendlines(1).DataLabel.Left = 1
.SeriesCollection(1).Trendlines(2).DataLabel.Top = .ChartArea.Height
.SeriesCollection(1).Trendlines(2).DataLabel.Left = .ChartArea.Width
End With
End If
End Sub[/vba]

brgipple · Mar 25th 2006

Re: Free Floating a Chart

Thanx Andy worked great!

Brian G

Free Floating a Chart

Participate now!

Share