jwise
03-13-2008, 09:04 AM
My workbook has about 40 worksheets. Each worksheet contains 4 rows of data with 12 values: Date(month), Occupancy percentage, Income, and Profit (all this is relative to apartment complexes). My job was to create 3 charts and fit them on the same page as the worksheet so the information could be analyzed. I have made this work, but I am having several difficulties.
The following code is the logic to produce the third of the three graphs, and it has many commented-out statements. I suspect all this is because I do not understand the chart object model. I only included this routine because the other 2 are identical, and I believe this clearly illuminates what I am doing (and not doing!).
'wkSheet.Rows("4:4").Select
Set Chart3 = Charts.Add
With Chart3
.ChartType = xlLineMarkers
.SetSourceData Source:=wkSheet.Range("1:1, 4:4"), PlotBy:= _
xlRows
.Location Where:=xlLocationAsObject, name:=wkSheet.name
'.HasTitle = True
'.ChartTitle.Characters.Text = proName
'.HasDataTable = True
'.DataTable.ShowLegendKey = True
End With
'ActiveChart.ChartType = xlLineMarkers
'ActiveChart.SetSourceData Source:=wkSheet.Range("1:1,4:4"), PlotBy:= _
' xlRows
'ActiveChart.Location Where:=xlLocationAsObject, name:=proName
'ActiveChart.ChartTitle.Characters.Text = proName
'ActiveChart.HasDataTable = True
'ActiveChart.DataTable.ShowLegendKey = True
Set Chart3 = Nothing
Q1: When the macro runs, all three embedded charts are stacked on top of each other. I have to edit each worksheet and separate them. How do I do this in a macro?
Q2: After macro execution, if I do "print preview", what I see is this third chart as if it were in a worksheet by itself. I discovered by accident that if I selectd cell "A1" (or any other cell) and then did the "print preview" that the displayed screen was what I expected (the worksheet numbers with the last chart stacked on top of charts 1 & 2). I then checked and noticed that rows 1 and 4 were selected in each sheet after macro execution. The disturbing thing is that I spent a lot of time trying to fix a problem I didn't have (creating a chart sheet instead of an embedded chart). Why is this? I am assuming that I could just "select" this cell in the macro and not go through this.
Q3: The very first statement ("wkSheet.Rows($1:$1, $4:$4).Select") causes an object error. I found this statement in some code via a Google search and assumed I needed it. I then commented it out. I am puzzled mainly because I think not having this causes grief later in this snippet. Do I need this?
Q4: The "'.HasTitle" and the rest of the "With" clause all cause errors. This is why I commented them out. Because I couldn't get this to work, I had to manually go through the charts and fix them. There would have been a huge time-savings if I could have gotten this to work. What did I do wrong?
Q5: A previous version of the code used the "Charts.Add" statement instead of the "Set Chart3 = Charts.Add". So, the last set of commented-out statements ("ActiveChart.ChartType = ..." were originally used. The equivalent statements to the "With" clause also failed. That is part of the reason I switched to the "With" clause. What do I need to do to get this stuff done in the macro so I don't have to manually do it?
The following code is the logic to produce the third of the three graphs, and it has many commented-out statements. I suspect all this is because I do not understand the chart object model. I only included this routine because the other 2 are identical, and I believe this clearly illuminates what I am doing (and not doing!).
'wkSheet.Rows("4:4").Select
Set Chart3 = Charts.Add
With Chart3
.ChartType = xlLineMarkers
.SetSourceData Source:=wkSheet.Range("1:1, 4:4"), PlotBy:= _
xlRows
.Location Where:=xlLocationAsObject, name:=wkSheet.name
'.HasTitle = True
'.ChartTitle.Characters.Text = proName
'.HasDataTable = True
'.DataTable.ShowLegendKey = True
End With
'ActiveChart.ChartType = xlLineMarkers
'ActiveChart.SetSourceData Source:=wkSheet.Range("1:1,4:4"), PlotBy:= _
' xlRows
'ActiveChart.Location Where:=xlLocationAsObject, name:=proName
'ActiveChart.ChartTitle.Characters.Text = proName
'ActiveChart.HasDataTable = True
'ActiveChart.DataTable.ShowLegendKey = True
Set Chart3 = Nothing
Q1: When the macro runs, all three embedded charts are stacked on top of each other. I have to edit each worksheet and separate them. How do I do this in a macro?
Q2: After macro execution, if I do "print preview", what I see is this third chart as if it were in a worksheet by itself. I discovered by accident that if I selectd cell "A1" (or any other cell) and then did the "print preview" that the displayed screen was what I expected (the worksheet numbers with the last chart stacked on top of charts 1 & 2). I then checked and noticed that rows 1 and 4 were selected in each sheet after macro execution. The disturbing thing is that I spent a lot of time trying to fix a problem I didn't have (creating a chart sheet instead of an embedded chart). Why is this? I am assuming that I could just "select" this cell in the macro and not go through this.
Q3: The very first statement ("wkSheet.Rows($1:$1, $4:$4).Select") causes an object error. I found this statement in some code via a Google search and assumed I needed it. I then commented it out. I am puzzled mainly because I think not having this causes grief later in this snippet. Do I need this?
Q4: The "'.HasTitle" and the rest of the "With" clause all cause errors. This is why I commented them out. Because I couldn't get this to work, I had to manually go through the charts and fix them. There would have been a huge time-savings if I could have gotten this to work. What did I do wrong?
Q5: A previous version of the code used the "Charts.Add" statement instead of the "Set Chart3 = Charts.Add". So, the last set of commented-out statements ("ActiveChart.ChartType = ..." were originally used. The equivalent statements to the "With" clause also failed. That is part of the reason I switched to the "With" clause. What do I need to do to get this stuff done in the macro so I don't have to manually do it?