| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find name of a chart
I am writing some vba code in Excel 2003 to use the histogram function
to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$ D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find name of a chart
On Mar 14, 7:46 pm, inquirer wrote:
I am writing some vba code in Excel 2003 to use the histogram function to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$ D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris Hello Chris, The new chart will always be the last one added to the collection. Yoou can return the name like this... N = ActiveSheet.ChartObjects.Count ChrtName = ActiveSheet.ChartObjects(N).Name Siincerely, Leith Ross |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find name of a chart
If you're just removing thm all, use
ActiveSheet.ChartObjects.Delete To make the ATP-related code run better, add a few lines to your code, right after the Application.Run "ATPVBAEN.XLA!Histogram" line. Dim sChtName As String ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname Now you can reference the chart object using ActiveSheet.ChartObjects(sChtname) instead of ActiveSheet.ChartObjects("Chart 1") The macro recorder capturees all of your mouse clicks, so you can streamline the codee a bit by making this kind of change: Dim sChtName As String Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$D$2:$D$733"), _ ActiveSheet.Range("$K$2"), _ ActiveSheet.Range("$J$3:$J$21"), _ False, False, True, False ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname With ActiveSheet.ChartObjects(sChtname) .Height = .Height * 2.98 ' I prefer to use actual dimensions, not scaling With .Chart .Legend.Delete ' othr chart formatting in here End With End With It will run faster this way, without flashing of the screen. See also my recent blog post, http://peltiertech.com/WordPress/200...ecorded-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "inquirer" wrote in message u... I am writing some vba code in Excel 2003 to use the histogram function to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$ D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find name of a chart
Jon Peltier wrote:
If you're just removing thm all, use ActiveSheet.ChartObjects.Delete To make the ATP-related code run better, add a few lines to your code, right after the Application.Run "ATPVBAEN.XLA!Histogram" line. Dim sChtName As String ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname Now you can reference the chart object using ActiveSheet.ChartObjects(sChtname) instead of ActiveSheet.ChartObjects("Chart 1") The macro recorder capturees all of your mouse clicks, so you can streamline the codee a bit by making this kind of change: Dim sChtName As String Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$D$2:$D$733"), _ ActiveSheet.Range("$K$2"), _ ActiveSheet.Range("$J$3:$J$21"), _ False, False, True, False ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname With ActiveSheet.ChartObjects(sChtname) .Height = .Height * 2.98 ' I prefer to use actual dimensions, not scaling With .Chart .Legend.Delete ' othr chart formatting in here End With End With It will run faster this way, without flashing of the screen. See also my recent blog post, http://peltiertech.com/WordPress/200...ecorded-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "inquirer" wrote in message u... I am writing some vba code in Excel 2003 to use the histogram function to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$ D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris Thanks for your help. I have made th changes Jon suggested and it works aok. I have other formatting to do to the chart: ActiveSheet.ChartObjects(sChtName).Activate ActiveChart.ChartTitle.Select Selection.Characters.Text = "Zone " & ws_name Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=4).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveChart.Axes(xlCategory).AxisTitle.Select Selection.Characters.Text = "RMR" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=3).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With This works fine but I gather it would be more efficient to include it in the With .Chart loop. I have tried to put it in there but without success. Could you show me the syntax please? Thanks Chris |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find name of a chart
Put this within the With .Chart/End With block in the snippet I posted
earlier. Lines I've marked with ' * can probably be removed because theey are restatements of default settings. With .ChartTitle .Characters.Text = "Zone " & ws_name .AutoScaleFont = False With .Characters(Start:=1, Length:=4).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False ' * .Superscript = False ' * .Subscript = False ' * .OutlineFont = False ' * .Shadow = False ' * .Underline = xlUnderlineStyleNone ' * .ColorIndex = xlAutomatic ' * End With End With With .Axes(xlCategory).AxisTitle .Characters.Text = "RMR" .AutoScaleFont = False With Selection.Characters(Start:=1, Length:=3).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False ' * .Superscript = False ' * .Subscript = False ' * .OutlineFont = False ' * .Shadow = False ' * .Underline = xlUnderlineStyleNone ' * .ColorIndex = xlAutomatic ' * End With End With - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "inquirer" wrote in message ... Jon Peltier wrote: If you're just removing thm all, use ActiveSheet.ChartObjects.Delete To make the ATP-related code run better, add a few lines to your code, right after the Application.Run "ATPVBAEN.XLA!Histogram" line. Dim sChtName As String ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname Now you can reference the chart object using ActiveSheet.ChartObjects(sChtname) instead of ActiveSheet.ChartObjects("Chart 1") The macro recorder capturees all of your mouse clicks, so you can streamline the codee a bit by making this kind of change: Dim sChtName As String Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$D$2:$D$733"), _ ActiveSheet.Range("$K$2"), _ ActiveSheet.Range("$J$3:$J$21"), _ False, False, True, False ' unique name for chart object sChtName = "HISTO_" & format(now,"yymmdd_hhmmss") activesheet.chartobjects(activesheet.chartobjects. count).name = sChtname With ActiveSheet.ChartObjects(sChtname) .Height = .Height * 2.98 ' I prefer to use actual dimensions, not scaling With .Chart .Legend.Delete ' othr chart formatting in here End With End With It will run faster this way, without flashing of the screen. See also my recent blog post, http://peltiertech.com/WordPress/200...ecorded-macro/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "inquirer" wrote in message u... I am writing some vba code in Excel 2003 to use the histogram function to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$ D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris Thanks for your help. I have made th changes Jon suggested and it works aok. I have other formatting to do to the chart: ActiveSheet.ChartObjects(sChtName).Activate ActiveChart.ChartTitle.Select Selection.Characters.Text = "Zone " & ws_name Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=4).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveChart.Axes(xlCategory).AxisTitle.Select Selection.Characters.Text = "RMR" Selection.AutoScaleFont = False With Selection.Characters(Start:=1, Length:=3).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With This works fine but I gather it would be more efficient to include it in the With .Chart loop. I have tried to put it in there but without success. Could you show me the syntax please? Thanks Chris |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find name of a chart
Many thanks, Jon. works well now
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find source WS from Chart | Excel Programming | |||
| find no fill series in chart | Excel Discussion (Misc queries) | |||
| how can i find the relation between x & y in chart | Charts and Charting in Excel | |||
| Chart how to find lastrow? | Excel Programming | |||
| Does "find and replace" work within a chart? | Charts and Charting in Excel | |||
Linear Mode
