Excel Chart Display With Different Resolutions

[jtrapat1]

We are using Office 97 and have a Microsoft Word macro which creates a report and pulls in an Excel bar chart and pie chart.
When the report is run with a resolution of 1024 X 768, the report looks great and is perfectly centered.
The problem is that when it is run with a resolution of 800 X 600, the pie charts are inserted off-center and nothing in the report lines up as in the previous report.

Does anyone know of a solution for this or do I have to write two different scripts for the two different resolutions?

Thanks In Advance
John

 

[lambuhere1]

John

I did experience a similar problem when I was working on some mapping tool. And one member in this group helped me out, I dont remember exactly his name, but I want to thank him for his code.

His mail is attached. Probably it shall guide you.

Several years ago, I ran into the same problem developing Excel apps
on a PC with one screen display resolution and then running them on
another with a different resolution.

My solution, and I don't know if it will work for you, was to
develop a macro to overcome the problem.

Here is the theory behind it:

1. Using an API function, detemine the user's PC height or
width setting in
pixels, and place it in a hidden cell.

2. Using the number returned above, set the zoom setting in
the activewindow.

3. Call the Resize_Screen macro from the Workbook_Open module.

Here is the complete code. Change the cell references as needed.
Note that I also set the zoom number in a cell. This is used if there
are other sheets in the workbook. When each sheet is activated, just
read the zoom setting in the primary sheet and set that sheet zoom the
same.
=========================================================
Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long

Const SM_CYSCREEN As Long = 1
Const SM_CXSCREEN As Long = 0

Sub Resize_Screen()
Dim lWidth As Long
Dim lHeight As Long

lWidth = GetSystemMetrics(SM_CXSCREEN)
lHeight = GetSystemMetrics(SM_CYSCREEN)
Application.ScreenUpdating = False
Range("r1&quot.Value = lWidth
If Range("r1&quot.Value = 640 Then
ActiveWindow.Zoom = 75
[r2] = 75
ElseIf Range("r1&quot.Value = 800 Then
ActiveWindow.Zoom = 100
[r2] = 100
ElseIf Range("r1&quot.Value = 1024 Then
ActiveWindow.Zoom = 125
[r2] = 125
Elseif Range("r1&quot.Value = 1280 Then
ActiveWindow.Zoom = 160
[r2] = 160

End If
End Sub