VBA to specify chart location as obj in current sheet?

[Guest]

Hi,

I have recorded the following...

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+f
'

Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveWindow.Visible = False
Windows("Master data.xls").Activate
End Sub

....this allows me to quickly plot a chart from the current sheet. How do I
specify that I want the chart as an object within the current sheet?

I removed the line...

ActiveChart.Location Where:=xlLocationAsObject, Name:="20305A.TXT"

....after I recorded the macro as it only places each graph in the original
worksheet, not the one I am working in. My book contains many sheets of data.

Can anyone please advise how I can change the macro above to work so that I
can go to any worksheet and run the macro, producing a plot of the data on
that sheet as an object in that sheet.

Cheers,

Dan

 

[Andy Pope]

Hi,

How about this,

Dim rngAC As Range

Set rngAC = ActiveCell
Columns("A:B").Select

With Charts.Add
.ChartType = xlXYScatterSmoothNoMarkers
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
.Location Where:=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

 

[Guest]

Thanks Andy, works great!

Hi,

How about this,

Dim rngAC As Range

Set rngAC = ActiveCell
Columns("A:B").Select

With Charts.Add
.ChartType = xlXYScatterSmoothNoMarkers
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
.Location Where:=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

 

[Jon Peltier]

I prefer

Dim chtob As ChartObject
Set chtob = ActiveSheet.ChartObjects.Add(Left, Top, Width, Height)
' where Left, etc., are coordinates in points
With chtob.Chart
' do your stuff here
End With

- Jon

 

[Andy Pope]

So do I Jon, if I know how and where the chart should be sized.

Cheers
Andy

 

[Guest]

I've tried to add what you both suggested was ideal, but I'm getting and
'expected End With' error... see below

What have I done wrong?

Cheers Chaps,

Dan


Sub Macro7()
'
' Macro7 Macro
' Macro recorded 23/02/2007 by School of Chemistry
'
' Keyboard Shortcut: Ctrl+j
'
Dim chtob As ChartObject
Set chtob = ActiveSheet.ChartObjects.Add(100, 100, 500, 400)
' where Left, etc., are coordinates in points
With chtob.Chart

With Charts.Add
.ChartType = xlXYScatterSmoothNoMarkers
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "time / s"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "current / A"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.Location Where:=xlLocationAsObject, Name:=rngAC.Parent.Name

Range("A1").Select


End Sub

 

[Andy Pope]

hmm. It's really one way or the other not both.
If you add a chartobject directly to the worksheet then you don't need
to change it's location. Also you don't need the Activechart reference
when using the With chtob.Chart


Sub Macro7()
'
' Macro7 Macro
' Macro recorded 23/02/2007 by School of Chemistry
'
' Keyboard Shortcut: Ctrl+j
'
Dim chtob As ChartObject

Set chtob = ActiveSheet.ChartObjects.Add(100, 100, 500, 400)
' where Left, etc., are coordinates in points
With chtob.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "time / s"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "current / A"
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
.HasLegend = False
.PlotArea.ClearFormats
.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End With
Range("A1").Select

End Sub