Prev: Pivot table error when Opening an Xml Spreadsheet from MS excel Xp
Next: Change category width...
From: Dr Dan on 23 Feb 2007 08:05
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
From: Dr Dan on 23 Feb 2007 09:53
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


 | 
Pages: 1
Prev: Pivot table error when Opening an Xml Spreadsheet from MS excel Xp
Next: Change category width...