Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a chart in a worksheet with VBA trouble
Hi there,
I'm experiencing trouble while selecting a chart object in a worksheet. With the code I included below, I generate an array of chart objects and put them on a sheet named "Charts". Those chart objects are named "Die(x,y)" where x and y are numerical values. If I want to select a single chart object in a sheet named "Charts", the macro recorder records: ActiveSheet.ChartObjects("Die(3,1)").Activate. But if I use this simple code, I get an errormessage 1004: Unable to get the ChartObjects property of the Worksheet class. Who can tell me what the problem can be? I also tried ActiveSheet.Shapes.Range("Die(3,1)").select but this seems not to work... I searched over the internet, but could not find a solution. Thanks in advance, Sebastiaan Maas. Sub TestCharts1() Dim myChart As ChartObject, ChartName As String, DataX() As Double, DataIb() As Double, DataIc() As Double Dim aNewSeries As Series, Temporary As String Sheets("Charts").Select For x = 0 To 7 For y = 0 To 7 OffsetX = x * 75 + 1 OffsetY = y * 75 + 1 ChartName = "Die(" & CStr(x + 2) & "," & CStr(8 - y) & ")" Set myChart = ActiveSheet.ChartObjects.Add(Left:=OffsetX, Width:=75, Top:=OffsetY, Height:=75) myChart.Name = ChartName myChart.Chart.ChartType = xlXYScatterLines If (x + y) Mod 2 = 0 Then 'Create checkerboard pattern myChart.Chart.ChartArea.Interior.ColorIndex = 19 End If 'Light yellow color Debug.Print x, y, myChart.Chart.Name, myChart.Name Temporary = myChart.Chart.Name Next y Next x ActiveSheet.Shapes.Range(Array("Die(2,8)", "Die(3,8)", "Die(4,8)", _ "Die(7,8)", "Die(8,8)", "Die(9,8)", "Die(2,7)", "Die(9,7)", _ "Die(2,1)", "Die(9,1)")).Select 'Delete obsolete graphs (no existing dies on the wafer) Selection.Delete With Wafer.Dies("3,1").DIETESTs(1).Modules("W140 (7335,775)").SCRIPTs("scripts\BJT1A.scr").Devices( 1).Algorithms(1) 'Data is stored in this dot structure ReDim DataX(.DATA(1).NumValues - 1) As Double, _ DataIb(.DATA(1).NumValues - 1) As Double, _ DataIc(.DATA(1).NumValues - 1) As Double 'Store all necessary data in local variables (Arrays) For i = 0 To .DATA(1).NumValues - 1 DataX(i) = .DATA(3).Value(i) DataIb(i) = .DATA(1).Value(i) DataIc(i) = .DATA(2).Value(i) Next i End With 'ActiveSheet.ChartObjects("Die(3,1)").Activate 'This generates Error 1004: Unable to get the ChartObjects property of the Worksheet class Sheets("Charts").ChartObjects("Die(3,1)").Activate 'This generates Error 1004: Unable to get the ChartObjects property of the Worksheet class 'And now select x-axis values = DataX and Y-axis value are DataIb and DataIc etc. etc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a chart in a worksheet with VBA trouble
Objects with names that include most types of punctuation are not accessible
at the Drawingobject level, or in your case as ChartObject. I would suggest don't include the brackets or the comma in "Die(2,8)", try instead something like "Die_2_8". But if you really must, or to get you out of trouble, go via Shapes. Eg Set myChartObject = mySheet.Shapes("Die(2,8)").DrawingObject or Set myChart = mySheet.Shapes("Die(2,8)").DrawingObject.Chart Probably better to start by attempting to set a Shape reference first, then the Chartobject or Chart. I haven't looked at the rest of your code but normally not necessary to Activate or Select the chart for whatever it is you want to do with it. Regards, Peter T "SebasFM" wrote in message ... Hi there, I'm experiencing trouble while selecting a chart object in a worksheet. With the code I included below, I generate an array of chart objects and put them on a sheet named "Charts". Those chart objects are named "Die(x,y)" where x and y are numerical values. If I want to select a single chart object in a sheet named "Charts", the macro recorder records: ActiveSheet.ChartObjects("Die(3,1)").Activate. But if I use this simple code, I get an errormessage 1004: Unable to get the ChartObjects property of the Worksheet class. Who can tell me what the problem can be? I also tried ActiveSheet.Shapes.Range("Die(3,1)").select but this seems not to work... I searched over the internet, but could not find a solution. Thanks in advance, Sebastiaan Maas. Sub TestCharts1() Dim myChart As ChartObject, ChartName As String, DataX() As Double, DataIb() As Double, DataIc() As Double Dim aNewSeries As Series, Temporary As String Sheets("Charts").Select For x = 0 To 7 For y = 0 To 7 OffsetX = x * 75 + 1 OffsetY = y * 75 + 1 ChartName = "Die(" & CStr(x + 2) & "," & CStr(8 - y) & ")" Set myChart = ActiveSheet.ChartObjects.Add(Left:=OffsetX, Width:=75, Top:=OffsetY, Height:=75) myChart.Name = ChartName myChart.Chart.ChartType = xlXYScatterLines If (x + y) Mod 2 = 0 Then 'Create checkerboard pattern myChart.Chart.ChartArea.Interior.ColorIndex = 19 End If 'Light yellow color Debug.Print x, y, myChart.Chart.Name, myChart.Name Temporary = myChart.Chart.Name Next y Next x ActiveSheet.Shapes.Range(Array("Die(2,8)", "Die(3,8)", "Die(4,8)", _ "Die(7,8)", "Die(8,8)", "Die(9,8)", "Die(2,7)", "Die(9,7)", _ "Die(2,1)", "Die(9,1)")).Select 'Delete obsolete graphs (no existing dies on the wafer) Selection.Delete With Wafer.Dies("3,1").DIETESTs(1).Modules("W140 (7335,775)").SCRIPTs("scripts\BJT1A.scr").Devices( 1).Algorithms(1) 'Data is stored in this dot structure ReDim DataX(.DATA(1).NumValues - 1) As Double, _ DataIb(.DATA(1).NumValues - 1) As Double, _ DataIc(.DATA(1).NumValues - 1) As Double 'Store all necessary data in local variables (Arrays) For i = 0 To .DATA(1).NumValues - 1 DataX(i) = .DATA(3).Value(i) DataIb(i) = .DATA(1).Value(i) DataIc(i) = .DATA(2).Value(i) Next i End With 'ActiveSheet.ChartObjects("Die(3,1)").Activate 'This generates Error 1004: Unable to get the ChartObjects property of the Worksheet class Sheets("Charts").ChartObjects("Die(3,1)").Activate 'This generates Error 1004: Unable to get the ChartObjects property of the Worksheet class 'And now select x-axis values = DataX and Y-axis value are DataIb and DataIc etc. etc. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a chart in a worksheet with VBA trouble
Thank you for your help. It works now!
Kind regards, Sebastiaan. "Peter T" <peter_t@discussions wrote in message ... Objects with names that include most types of punctuation are not accessible at the Drawingobject level, or in your case as ChartObject. I would suggest don't include the brackets or the comma in "Die(2,8)", try instead something like "Die_2_8". But if you really must, or to get you out of trouble, go via Shapes. Eg Set myChartObject = mySheet.Shapes("Die(2,8)").DrawingObject or Set myChart = mySheet.Shapes("Die(2,8)").DrawingObject.Chart Probably better to start by attempting to set a Shape reference first, then the Chartobject or Chart. I haven't looked at the rest of your code but normally not necessary to Activate or Select the chart for whatever it is you want to do with it. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble Selecting a Range in VBA | Excel Programming | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Trouble selecting chart in macro | Excel Programming | |||
Selecting and moving chart(s) within a worksheet using keyboard ke | Charts and Charting in Excel | |||
trouble selecting a named cell | Excel Programming |