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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
It does appear to be a bug.
In terms of trying to find a workaround, what effect are you actually trying to achieve?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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
Back
Top