Solved

Use VBA to Change gradient style in Access 2010 report graph.

Posted on 2010-08-13
14
1,951 Views
Last Modified: 2013-11-28
I have a report in Access 2010 that contains a graph. I need to change the color and gradient effects of a data sereies programmatically. I can change the color but when I try to change the gradient I get the error "object does not support this property or method". I am using the code below. What am I doing wrong?

Private Sub Report_Load()
    Dim SummGraph As Chart
    Dim pt As ChartGroup
 
    Set SummGraph = Me.SummaryGraph.Object.Application.Chart  'initialize things to a normal look
    With SummGraph.SeriesCollection(1)
        .Interior.Color = RGB(255, 0, 0)
        .OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3
    End With
    
End Sub

Open in new window

0
Comment
Question by:lizziesboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 33430417
Try
.Fill.OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3

Note the .Fill.
Gradient is a fill property, in other words, not directly a series property. ChartArea and other sections of the chart each have their own Fill property which can be modified with gradients.
0
 

Author Comment

by:lizziesboy
ID: 33430623
That gives me the error "application-defined or object-defined error".
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33430825
According to this thread:
http://www.ozgrid.com/forum/showthread.php?t=141878&page=1

It's actually .Format.Fill for series collections. So you'd want this line instead:

.Format.Fill.OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:lizziesboy
ID: 33431214
I still get "object does not support this property or method" when I try that.
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33431352
Getting the properties in the right order and layer for charts in VBA is always a pain in my experience.

Which line is the error actually happening at? Is it happening at the With statement or at the Gradient statement?

I'm trying to reproduce your error and am getting a different error entirely. Of course, I don't have your setup, either.
0
 

Author Comment

by:lizziesboy
ID: 33431876
it is bombing at the line
        .OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3
Without that line the color change works.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33433160

Have you tried declaring the chart as an "Object"?

The thing with charts is that you will get odd errors if certain things just cant be done.

For example, you cannot "easily" get a datapoint values with the Chart Labels turned off.

Also a lot of chart objects are arrays so you may not be able to Get or Set them directly
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33433172
I guess that there is a reason why you cant do this in the chart options dialog box?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33435112
Check your syntax, you forgot the ".Fill"


.Fill.OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3



0
 

Author Comment

by:lizziesboy
ID: 33436798
It turns out that my copy of access needed to be repaired. I'm going to split the points among all of you.
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33466722
Thanks for the points, but I thought you were planning on splitting them?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33467639
LOL,
telyni19, I just noticed that in your first post you noticed that "Fill" was missing.
So you get the points...
As a budding new Expert here, you could use them more than me.
;-)
Jeff
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33467984
Well, and then when that didn't work, I gave a different answer that probably wouldn't have been right. I wasn't certain of the answer, only first. But I appreciate your graciousness at the outcome.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33468193
;-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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