Solved

How to set the top argument of the ChartObjects.Add method dynamically

Posted on 2015-02-03
15
91 Views
Last Modified: 2016-02-11
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.Add(Left:=75, Width:=750, Top:=150, Height:=300).Chart


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

Open in new window

0
Comment
Question by:chtullu135
  • 10
  • 4
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40586711
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.
Is this in a Report r a form?
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40586782
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
0
 

Author Comment

by:chtullu135
ID: 40587117
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40587143
how do you populate the datatable?
0
 

Author Comment

by:chtullu135
ID: 40587266
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40587283
<I populate the datatable from a query>

are you using vba to do this or copy and paste
0
 

Author Comment

by:chtullu135
ID: 40587307
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40587363
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
0
 

Author Comment

by:chtullu135
ID: 40587365
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
0
 

Author Comment

by:chtullu135
ID: 40587372
objXlDataSheet.UsedRange.rows.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
0
 

Author Comment

by:chtullu135
ID: 40587392

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.
0
 

Author Comment

by:chtullu135
ID: 40618128
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.
0
 

Author Comment

by:chtullu135
ID: 40618127
I'd like to cancel my request to close the question.  I meant to give the point to Rey
0
 

Author Comment

by:chtullu135
ID: 40618129
I'd like to cancel my request to close the question.  I meant to award the points to rey.
0
 

Author Closing Comment

by:chtullu135
ID: 40618130
Thanks Rey
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now