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

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

lizziesboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

telyni19Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lizziesboyAuthor Commented:
That gives me the error "application-defined or object-defined error".
0
telyni19Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

lizziesboyAuthor Commented:
I still get "object does not support this property or method" when I try that.
0
telyni19Commented:
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
lizziesboyAuthor Commented:
it is bombing at the line
        .OneColorGradient msoGradientHorizontal, Variant:=1, Degree:=0.3
Without that line the color change works.
0
Jeffrey CoachmanMIS LiasonCommented:

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
Jeffrey CoachmanMIS LiasonCommented:
I guess that there is a reason why you cant do this in the chart options dialog box?
0
Jeffrey CoachmanMIS LiasonCommented:
Check your syntax, you forgot the ".Fill"


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



0
lizziesboyAuthor Commented:
It turns out that my copy of access needed to be repaired. I'm going to split the points among all of you.
0
telyni19Commented:
Thanks for the points, but I thought you were planning on splitting them?
0
Jeffrey CoachmanMIS LiasonCommented:
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
telyni19Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.