Userform Problem :(


New Member
Aug 24, 2020
Office Version
  1. 2016
  1. Windows
Hi Guys!
I created a chart in a worksheet and displayed it in the Userform.

Firstly, my problem is when I zoom in on the chart worksheet, the userform image of the chart will be bigger. When i zoom out, the chart becomes smaller on the userform. As such, I set the zoom to 65% on that worksheet. Any reasons why this happens?

Secondly, whenever i pressed on a cell with numbers in the Chart sheet, the graph gets messed up. How do I solve this?

Thirdly, I want to display a logo on the graph and display it on userform. However, it is not possible as the userform wont show anything but the chart. As such, I wanted to use VBA to set a logo background picture of the graph.
However, i do not want the excel to have two files. One containing the excel itself and one for the logo. Any way to come this?

Appreicate if anyone could help me! Have a nice day!

Here's the code!

Private Sub CommandButton1_Click()

Dim MyChart As Chart
Dim ChartDataX As Range
Dim ChartDataY As Range
Dim throw As Double
Dim imageName As String

Application.ScreenUpdating = False

ActiveWindow.Zoom = 65


Sheets("Chart").Range("C1").Value = "=Calculator!E34/0.5"
Sheets("Chart").Range("B2").Value = "=Round(Formula!W7, 1)"
Sheets("Chart").Range("A2").Value = "0"
Sheets("Chart").Range("A3").Value = "=chart!A2+0.5"
Sheets("Chart").Range("b3").Value = "=Round(SQRT((PI()*Formula!$T$4^2/4/(Formula!$P$4+Formula!$T$4))/(SQRT(PI())*0.077*chart!A3)*Formula!$W$7^2)/100*(100+Formula!$O$15),2)" 'FOR CIRCULAR

If Sheets("calculator").Range("E34").Value > 0.5 Then

throw = Sheets("Chart").Range("C1").Value
Worksheets("Chart").Range("A3").AutoFill Destination:=Worksheets("Chart").Range(Worksheets("Chart").Cells(3, 1), Worksheets("Chart").Cells(throw + 2, 1)), Type:=xlFillDefault
Worksheets("Chart").Range("B3").AutoFill Destination:=Worksheets("Chart").Range(Worksheets("Chart").Cells(3, 2), Worksheets("Chart").Cells(throw + 2, 2)), Type:=xlFillDefault

Application.CutCopyMode = False
Set MyChart = Sheets("Chart").Shapes.AddChart2(227, xlLine).Chart
MyChart.ChartTitle.Text = "Velocity vs Throw Distance Graph"

Set ChartDataX = Sheets("chart").Range(Worksheets("Chart").Cells(3, 1), Worksheets("Chart").Cells(throw + 3, 1))
Set ChartDataY = Sheets("chart").Range(Worksheets("Chart").Cells(3, 2), Worksheets("Chart").Cells(throw + 3, 2))

MyChart.FullSeriesCollection(1).XValues = ChartDataX 'X AXIS
MyChart.FullSeriesCollection(1).Values = ChartDataY 'Y AXIS

MyChart.SeriesCollection(1).Smooth = True

MyChart.ChartStyle = 234



With MyChart.ChartTitle
.Format.TextFrame2.TextRange.Font.Size = 30
.Font.Name = "arial"
End With

With MyChart.Axes(xlValue)

.HasTitle = True
With .AxisTitle
.Caption = "Velocity (m/s)"
.Font.Name = "arial"
.Font.Size = 18
End With

End With

With MyChart.Axes(xlPrimary)

.HasTitle = True

With .AxisTitle
.Caption = "Throw Distance (m)"
.Font.Name = "arial"
.Font.Size = 18
End With

End With

With MyChart.Axes(xlCategory).TickLabels.Font '(X Axis Number)
.Bold = msoTrue
.Size = 18
End With

MyChart.SetElement (msoElementPrimaryValueAxisShow)

With MyChart.Axes(xlValue).TickLabels.Font '(Y Axis Number)
.Bold = msoTrue
.Size = 16
End With

With MyChart.SeriesCollection(1) '(Label Number)

.DataLabels.Format.TextFrame2.TextRange.Font.Size = 16

.DataLabels.Format.TextFrame2.TextRange.Font.Bold = True
End With

With MyChart.Axes(xlValue).Format.Line '(Solid Line Y Axis)
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0

End With

MyChart.Axes(xlValue).MajorTickMark = xlNone

With MyChart.Axes(xlCategory).Format.Line '(Solid Line X Axis)
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
End With

Worksheets("chart").ChartObjects(1).Width = 1058
Worksheets("chart").ChartObjects(1).Height = 560
'MyChart.Axes(xlValue).HasMajorGridlines = True
'MyChart.Axes(xlCategory).HasMajorGridlines = True


imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"
MyChart.Export Filename:=imageName

Application.ScreenUpdating = True

Userform1.Image1.Picture = LoadPicture(imageName)

End If
End Sub

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Watch MrExcel Video

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