ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   VBA to specify chart location as obj in current sheet? (https://www.excelbanter.com/charts-charting-excel/132099-vba-specify-chart-location-obj-current-sheet.html)

Dr Dan[_2_]
VBA to specify chart location as obj in current sheet?
 
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 Whe=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
VBA to specify chart location as obj in current sheet?
 
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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

Dr Dan wrote:
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 Whe=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, Microsoft MVP - Excel
http://www.andypope.info

Dr Dan[_2_]
VBA to specify chart location as obj in current sheet?
 
Thanks Andy, works great! :)

"Andy Pope" wrote:

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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

Dr Dan wrote:
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 Whe=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, Microsoft MVP - Excel
http://www.andypope.info

Jon Peltier
VBA to specify chart location as obj in current sheet?
 
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dr Dan" wrote in message
...
Thanks Andy, works great! :)

"Andy Pope" wrote:

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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

Dr Dan wrote:
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 Whe=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, Microsoft MVP - Excel
http://www.andypope.info

Andy Pope
VBA to specify chart location as obj in current sheet?
 
So do I Jon, if I know how and where the chart should be sized.

Cheers
Andy

Jon Peltier wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dr Dan" wrote in message
...

Thanks Andy, works great! :)

"Andy Pope" wrote:


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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name
End With

Cheers
Andy

Dr Dan wrote:

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 Whe=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, Microsoft MVP - Excel
http://www.andypope.info





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Dr Dan[_2_]
VBA to specify chart location as obj in current sheet?
 
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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name

Range("A1").Select


End Sub

Andy Pope
VBA to specify chart location as obj in current sheet?
 
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


Dr Dan wrote:
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 Whe=xlLocationAsObject, Name:=rngAC.Parent.Name

Range("A1").Select


End Sub



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


All times are GMT +1. The time now is 11:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com