Global macro for resizing and reformatting Excel charts

[Sol Apache]

I am using Excel 2004 (Mac) to create an Excel template for charts to be
copied into PPT and Word - all to be used on Windows.

I have created a macro which reformats a chart (removes the unwanted black
border which Excel automatically imposes upon user-defined charts), then I
resize the chart to the size I want (a choice of three sizes which the user
selects using a userform). This means I have to deselect the chart, hold the
shift key down to make the chart an object that can be resized.

The problem is that the recorded macro refers specifically to a worksheet
and a chart and in my recording I had to deselect the chart in order to hold
the shift key down.

I want the macro to work on any selected chart created in a
workbook based on this template. I also want it to go back to ordinary
selection again so I can copy the chart as a picture.

I have very rarely used macros in Excel, and so I know very little about the
process - except that recorded macros are quite difficult to use globally.

So I¹d very much appreciate any help on this problem.


Sol

PS Would it help if I provided the VB text I have created already?

 

[Robin Hammond]

Sol,

If you have the alternative of doing this on a windows box you could have a
look at the Chart Browser in my XspandXL add-in:
http://www.enhanceddatasystems.com/ED/Pages/XspandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

 

[Sol Apache]

Sorry Robin, I can¹t access a windows box and even if I did I don¹t think
the client would be interested in buying XspandXL for its Excel users.

I think it looks interesting though. Are you ever going to do a Mac version?

Is there no simple way of making a global change chart size macro?

Thanks

Sol

 

[Robin Hammond]

Sol,

This should get you heading in the right direction.

Sub ComplexDemo()
Dim lCounter As Long
With ActiveSheet
For lCounter = 1 To .ChartObjects.Count
ResizeChartAndCopy .ChartObjects(lCounter).Chart, 300, 200,
Sheets(2), lCounter
Next lCounter
End With
End Sub

Sub SimpleDemo()
ResizeChartAndCopy ActiveChart, 300, 200, Sheets(2)
End Sub

Public Sub ResizeChartAndCopy(chInput As Chart, _
lWidth As Long, _
lHeight As Long, _
shDestination As Worksheet, _
Optional lChartCounter As Long = 1)
'---------------------------------------------------------------------------------------
' Procedure : ResizeChartAndCopy
' DateTime : 10/21/2005 07:54
' Author : Robin Hammond
' Purpose : chInput is the chart you want to copy
' lWidth is desired width of the chart
' lHeight is desired height of the chart
' shDestination is the target worksheet for the copied picture
' lChartCounter is a counter used to position the chart on the
target sheet
'---------------------------------------------------------------------------------------
'
Dim lOffsetRow As Long
With chInput.Parent
.Border.LineStyle = xlNone
.Width = lWidth
.Height = lHeight
End With
chInput.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:= _
xlPicture
With shDestination
lOffsetRow = 1 + ((lChartCounter - 1) * ((lHeight + .Rows(1).RowHeight)
_
/ .Rows(1).RowHeight))
.Paste .Cells(lOffsetRow, 1)
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[Sol Apache]

Robin

Thank you very much for replying in such detail to my request. All that I
needed was this one line:

ResizeChartAndCopy ActiveChart, 300, 200, Sheets(2)

The rest I think I can record, and amending the measurements.

I wonder why it is so hard to find things in the online help. I searched for
³resize² and ³resize chart² and nothing so simple, or appropriate, could I
find.


Sol

PS You never replied that you would be doing a Mac version of your Excel
enhancements. Will you be waiting until Macs use Intel in about a year¹s
time?

 

[Robin Hammond]

Sol,

Glad that helped.

The ChartBrowser uses some quite complex API calls to speed up the loading
of the chart images you can see on the screen shots. I'm sure it would be
possible to shift those over to a Mac somehow but I haven't had one for 12
years, and would not know where to start. Otherwise, most of it should run
fine, but I don't have the time to do a conversion.

Still, if anyone wants to give a mac port a go, let me know...

Robin Hammond
www.enhanceddatasystems.com