VBA to change chart porperties

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67
I am looking at the VBA code to be to be able to change the Stretch options of a column chart.

When you right click on a column in a chart then select "Format Data Point" then select "Fill" then select "Picture or Texture fill" you have a section call "Stretch Options - Offsets" where you can enter % for left, right top and bottom. I would like to know the vba code to change these percentages.

Thanks in advance.
Best Regards,
Alain
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,770
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The properties I think you want (see this post) are the TextureOffsetX, TextureOffsetY, TextureHorixontalScale and TextureVerticalScale but I cannot for the life of me get them to work with a chart in 2010.
 

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67
This is the code I am using:
Code:
Sub AddImage()

r = Range("Point_Start").Row
c = Range("Point_Start").Column
p = 1
Do Until Cells(r, c).Value = ""
     tt = Cells(r, c + 1).Value

    Select Case tt
    
    Case Is > Range("case1").Value
        Shape = "Rectangle_green"
    Case Is >= Range("case2").Value
        Shape = "Rectangle_yellow"
    Case Is < Range("case3").Value
        Shape = "Rectangle_red"
    End Select
    
    Sheets("ref").Shapes(Shape).Copy
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Points(p).Paste
    
    With ActiveChart.SeriesCollection(1).Points(p).Format.Fill
        
        .TextureOffsetY = 0.276
        .TextureOffsetX = 0.276
        .TextureHorizontalScale = 0.276
        .TextureVerticalScale = 0.276
 
    End With

r = r + 1
p = p + 1
Loop

End Sub
The image get pasted but I cannot make the image to stretch.

Regards,
Alain
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,770
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As I said, I cannot get them to actually work with a chart in 2010. It may be a bug. I'll see if one of the chart gurus knows about this.
 

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67

ADVERTISEMENT

Thanks for your help. For info I have excel 2007.

Regards,
Alain
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,770
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It does appear to be a bug.
In terms of trying to find a workaround, what effect are you actually trying to achieve?
 

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67

ADVERTISEMENT

The columns' amount represent the profit and I am using the offset left and right % to set the column's width based on the volume of sales.

I know how to do this manually but now I like to write a macro to have it automated.

Best Regards,
Alain
 

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67
Rory,

Did you manage to get info from the chart gurus ?

Regards,
Alain
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top