Juan Velasquez
asked on
How to set the top argument of the ChartObjects.Add method dynamically
Hello,
I have a worksheet that consists of a datatable and five charts. The table is dynamic in size. I'lm trying to modify my code so that the charts will move up or down so that the distance between the charts and the datatable remains constant. I am creating the charts and in fact via automation from Access. Everything populates fine. I think I may have to use the current.region property in the top argument but am not sure how. In addition, I'm not sure how to convert the currentregion to points.
Set objXlChart = objXLSheet.ChartObjects.Ad d(Left:=75 , Width:=750, Top:=150, Height:=300).Chart
I have a worksheet that consists of a datatable and five charts. The table is dynamic in size. I'lm trying to modify my code so that the charts will move up or down so that the distance between the charts and the datatable remains constant. I am creating the charts and in fact via automation from Access. Everything populates fine. I think I may have to use the current.region property in the top argument but am not sure how. In addition, I'm not sure how to convert the currentregion to points.
Set objXlChart = objXLSheet.ChartObjects.Ad
Private Sub CreateNE_TotalPercentItemsAvailableChart(objWS As Object)
Dim objXLSheet As Object
Dim objXlChart As Object
Dim objXlDataSheet As Object
Dim rng As Object
Dim rngChart As Object
Set objXLSheet = objWS
Set objXlDataSheet = objXlBook.Worksheets("Summary")
Set rng = objXlDataSheet.Range("A:A,D:D")
Set objXlChart = objXLSheet.ChartObjects.Add(Left:=75, Width:=750, Top:=150, Height:=300).Chart
With objXlChart
.ChartType = 4 'xlLine
.SetSourceData Source:=rng
.PlotBy = 2
.HasTitle = True
.ChartTitle.Text = "Norgren Express" & vbCrLf & "Total Items % Available"
.HasLegend = False
.Axes(2).TickLabels.NumberFormat = "0%"
.Axes(2).TickLabels.Orientation = 0
.Axes(1).TickLabels.Orientation = 60
.ChartStyle = 31
End With
End Sub
convert the value of the Top parameter to a variable.
dim xTop as long
xTop=150 ' start of the chart vertical location
then just add the height of the chart + space to xTop variable as you are plotting the succeeding charts
dim xTop as long
xTop=150 ' start of the chart vertical location
then just add the height of the chart + space to xTop variable as you are plotting the succeeding charts
ASKER
convert the value of the Top parameter to a variable.
dim xTop as long
xTop=150 ' start of the chart vertical location
then just add the height of the chart + space to xTop variable as you are plotting the succeeding charts
I want to be able to set the value of xtop for the first chart equal to the height of the currentregion of the datatable (which is the datasource for the graphs and which may expand according to the underlying recordset" plus a space of 50 points
how do you populate the datatable?
ASKER
I populate the datatable from a query. It's jus a dataset that is used to populate a chart. Depending on the data, it can be more or less rows.
<I populate the datatable from a query>
are you using vba to do this or copy and paste
are you using vba to do this or copy and paste
ASKER
I am using vba. I have a query and I am using the copy recordset method to put the results of the query on the worksheet. I am then generating several charts from that data. I want to ensure that no matter how many rows of data are returned that the charts do not end up overlapping one another or the dataset at the top of the worksheet.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've attached a screenshot of the worksheet. I want to dynamically position the top position of each chart so that they will never overlap
Worksheet.jpg
Worksheet.jpg
ASKER
objXlDataSheet.UsedRange.r ows.count
From reading about the top argument, it uses points. Is there a way to convert the rows count to point? It seems like they are apples and oranges
From reading about the top argument, it uses points. Is there a way to convert the rows count to point? It seems like they are apples and oranges
ASKER
after the copyfromrecordset line,
you can get the number of rows that was used with
objXlDataSheet.UsedRange.rows.count
from here you can based your starting xTop variable
I see what you mean. I'll give it a try.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for chtullu135's comment #a40587392
for the following reason:
Thank you very much for your help.
Accepted answer: 0 points for chtullu135's comment #a40587392
for the following reason:
Thank you very much for your help.
ASKER
I'd like to cancel my request to close the question. I meant to give the point to Rey
ASKER
I'd like to cancel my request to close the question. I meant to award the points to rey.
ASKER
Thanks Rey
What about page breaks?
Move the chart?, ...or do you mean shrink/grow?
This is all difficult to visualize...
Can you post a screenshot of what you have now, ...then post a clear graphical example of what you want it to be...?
I have done something like this for Images,...so the same technique might be useful here.
JeffCoachman