How to fix chart size regardless of zoom?

[Kelvin Stott]

Hello, I hope somebody can help:

I've written some VBA code to create a chartobject and then save it as a GIF file, which is then loaded into a user form. This all works well and the image looks perfect - however, if I change the zoom of the active worksheet before running the code, the size and resolution of the chart object and resulting image file are changed, so that it looks really bad when loaded into the user form.

Is there any way to fix the size and resolution of the chart object and image, regardless of the current zoom, so that I get consistent & predictable results?

I also tried creating the chart as a chartsheet (rather than a chart object on the current worksheet), but that has its own problems as all the text and image proportions are messed up.

Please help, thanks!

 

[Kelvin Stott]

Right click on the chart and select "Chart Window"

Thanks, I'll record a macro and see what I can do with the code there, see what method works best...

 

[Kelvin Stott]

Got it, thanks! Will report back soon ...

It's strange, but even when I reset the zoom just before and after export, I still see the sheet zoom in/out, and even see the temporary chart object in the background for a split second. Maybe it's the export that's too slow.

I'll try the window method now...

 

[Jaafar Tribak]

It's strange, but even when I reset the zoom just before and after export, I still see the sheet zoom in/out, and even see the temporary chart object in the background for a split second. Maybe it's the export that's too slow.

I'll try the window method now...

Does that happen even when turning the screen updating off ?

Application.ScreenUpdating = False

[COLOR=SeaGreen]' Create the chart here...[/COLOR]

ActiveWindow.Zoom = 100

[COLOR=SeaGreen]'Export chart here...[/COLOR]

[COLOR=SeaGreen]'Restore initial Zoom here ...[/COLOR]

Application.ScreenUpdating = True

 

[Kelvin Stott]

Does that happen even when turning the screen updating off ?

Application.ScreenUpdating = False

[COLOR=SeaGreen]' Create the chart here...[/COLOR]

ActiveWindow.Zoom = 100

[COLOR=SeaGreen]'Export chart here...[/COLOR]

[COLOR=SeaGreen]'Restore initial Zoom here ...[/COLOR]

Application.ScreenUpdating = True

Actually, with screenupdating off I don't see the temporary zoom reset (which is good), but I do see the temporary chartobject created in the background (not good).

 

[Jaafar Tribak]

Actually, with screenupdating off I don't see the temporary zoom reset (which is good), but I do see the temporary chartobject created in the background (not good).

Yes I missed that

Place Application.ScreenUpdating = False AFTER creating the chart.

 

[Kelvin Stott]

Yes I missed that

Place Application.ScreenUpdating = False AFTER creating the chart.

This works perfectly now, thank you:

    Call CreateDChart 'creates chart object
    
    Application.ScreenUpdating = False 'turns off screen updating
    ChartScale = ActiveWindow.Zoom 'stores current zoom
    ActiveWindow.Zoom = 100 'resets zoom to 100%
    TempChart = ThisWorkbook.Path & "\tempchart.bmp"
    DChart.Export TempChart 'exports chart image
    ActiveWindow.Zoom = ChartScale 'resets zoom to original
    DChart.Parent.Delete 'deletes chart object
    Application.ScreenUpdating = True 'turns back on screen updating
    DControl.ChartImage.Picture = LoadPicture(TempChart) 'loads saved image into userform
    Kill TempChart 'removes temp chart file
    
    DControl.Show 'shows user form

PS. I don't seem to have the option to create floating chart window with right-click chart. Is this normal? I'm on Excel 2007, updated to 2010.

 

[Jaafar Tribak]

This works perfectly now, thank you:

    Call CreateDChart 'creates chart object
    
    Application.ScreenUpdating = False 'turns off screen updating
    ChartScale = ActiveWindow.Zoom 'stores current zoom
    ActiveWindow.Zoom = 100 'resets zoom to 100%
    TempChart = ThisWorkbook.Path & "\tempchart.bmp"
    DChart.Export TempChart 'exports chart image
    ActiveWindow.Zoom = ChartScale 'resets zoom to original
    DChart.Parent.Delete 'deletes chart object
    Application.ScreenUpdating = True 'turns back on screen updating
    DControl.ChartImage.Picture = LoadPicture(TempChart) 'loads saved image into userform
    Kill TempChart 'removes temp chart file
    
    DControl.Show 'shows user form

PS. I don't seem to have the option to create floating chart window with right-click chart. Is this normal? I'm on Excel 2007, updated to 2010.

Glad it is now working for you.

I can't find that option either. I am sure I had it on excel 2003 and I have used it before. hope someone can pop in and tell us where that option is in excel 2007/2010.

 

[Kelvin Stott]

I can't find that option either. I am sure I had it on excel 2003 and I have used it before. hope someone can pop in and tell us where that option is in excel 2007/2010.

Me too.

Meanwhile, thanks again for all your help, I really appreciate it!