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?


On 21/10/05 01:21, in article , "Robin
Hammond" wrote:

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" wrote in message
...
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




On 20/10/05 01:55, in article ,
"Robin
Hammond" wrote:

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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Sol Apache" wrote in message
...
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?