Userform Problem :(

ClevelandsZy

New Member
Joined
Aug 24, 2020
Messages
1
Office Version
  1. 2016
Platform
  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

Sheets("chart").Activate
ActiveWindow.Zoom = 65


Sheets("Chart").Range("A2:B1260").ClearContents


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.SeriesCollection.NewSeries
MyChart.FullSeriesCollection(1).XValues = ChartDataX 'X AXIS
MyChart.FullSeriesCollection(1).Values = ChartDataY 'Y AXIS

MyChart.SeriesCollection(1).Smooth = True

MyChart.ChartStyle = 234

'-------------------------------------------------------------------------------------------------------------------------

Worksheets("chart").ChartObjects(1).Activate

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

Sheets("chart").ChartObjects(1).Delete
Application.ScreenUpdating = True

Userform1.Image1.Picture = LoadPicture(imageName)
Worksheets("Calculator").Activate

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

Threads
1,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

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