Proper sizing of graph picture on userform image control


Active Member
Sep 9, 2016
Good day all,

I have an issue that kindof self corrects (which is more perplexing than having a set problem). Anyhow, I'll try my best to explain. I'm running the classic sub routine of taking a chart, exporting it as a picture (jpeg), and loading the picture into an image control. I've determined that setting the Zoom control of the worksheet is an important aspect of proper sizing. Just a note, I've set the PictureSizeMode property of the image control to 0-fmPictureSizeModeClip as this is the only setting that produces a non-distorted image (i.e. it looks the best).

So from some trial and error, I've determined that the chart picture will size perfectly at a Window.Zoom value of 120. The thing is, it works only if I run the subroutine TWICE. I haven't the foggiest why this is so. If I run it once, the picture is too small, if I run it twice (and all subsequent runs) it sizes perfectly.

Any thoughts of what's going on? (p.s. sorry for the identing on the code below, my indented code doesn't paste indented and I don't know how to do that).

VBA Code:
Private Sub MakeChart()
'--creates a new chart, makes a jpg image and displays the image
Dim myChart As Chart
Dim dblZoomSave As Double
Dim ImageName As String, sCurrentSheet As String
Dim i As Integer
On Error Resume Next
Application.ScreenUpdating = False
sCurrentSheet = ActiveSheet.Name
'--store current zoom
dblZoomSave = ActiveWindow.Zoom
ActiveWindow.Zoom = 120
'User defined scatter or line chart
If Me.obPoint.value = True Then
Set myChart = ActiveSheet.Shapes.AddChart(xlXYScatter).Chart
Set myChart = ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart
End If
'Uses .Values stored in the Collection
With myChart
For i = 0 To Me.lbTags.ListCount - 1
With .SeriesCollection.NewSeries
.XValues = mrXValues
.values = mrangeCollection(i + 1)
.Name = Me.lbTags.List(i)
.MarkerSize = 4
End With
Next i
.HasLegend = True
.HasTitle = True
.HasTitle = False
.Legend.Position = xlLegendPositionBottom
If Me.cbLabels.value = True Then

End If
With .Legend
.Font.Size = 8
  End With
'Some standard formatting with axis labels
With .Axes(xlCategory)
If CDate(Me.lblEndDate.Caption) - CDate(Me.lblStartDate.Caption) < 365 Then
.TickLabels.NumberFormat = "[$-409]mmm-dd;@"
.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
End If
.TickLabels.Font.Size = 9
  End With

With .Axes(xlValue)
.TickLabels.NumberFormat = "#,##0"
.HasTitle = True
.AxisTitle.Text = "Number of Employees"
End With
End With

'Export File and Store in Userform Picture control
ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
myChart.Export Filename:=ImageName

Application.DisplayAlerts = False
Application.DisplayAlerts = True
'--reset zoom and sheet
ActiveWindow.Zoom = dblZoomSave
Application.ScreenUpdating = True
'Place on userform
Dim frm As GraphHR
Set frm = New GraphHR
frm.Image1.Picture = LoadPicture(ImageName)
frm.Show vbModeless
On Error GoTo 0
End Sub

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

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
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 "".
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