Proper sizing of graph picture on userform image control

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
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
ThisWorkbook.Worksheets("ReportHistory").Activate
ThisWorkbook.Worksheets("ReportHistory").Range("A44").Select
'--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
Else
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
.ApplyDataLabels

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;@"
Else
.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
ActiveSheet.ChartObjects(1).Delete
Application.DisplayAlerts = True
'--reset zoom and sheet
ActiveWindow.Zoom = dblZoomSave
ThisWorkbook.Worksheets(sCurrentSheet).Activate
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

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 MrExcel.com.
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 "mrexcel.com".
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
Top