VBA Chart resize code not working with error: the shape is locked and cannot be resized

I have finally found a code that meets my needs to create a chart (Code below). However I have two problems with that:

1- Worst thing is that every time I run the code it makes 2 sheets on my workbook. One contains a blank chart frame and one contains the chart itself. The latter, thanks to the code becomes hidden but anyways, after 5 times running the code I have 10 new sheets on my workbook that have to go and delete all of them. (This is now solved in comments below the post)

2- I can not resize it with any resizing code that I tried. Here I have .ChartArea.Height and .ChartArea.Width which gives me the error: the shape is locked and cannot be resized.

Is there any practical way to control the size really?

Private Sub CommandButton4_Click()

Charts.Add

chartarray1 = Array(Val(UserForm1.TextBox6.Value), Val(UserForm1.TextBox7.Value))
chartarray2 = Array("methane", "carbon")

Dim mychart As Chart
Dim fname As String

Set mychart = Charts.Add
With mychart
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "emissions"
.SeriesCollection(1).XValues = chartarray2
.SeriesCollection(1).values = chartarray1
.ChartType = xlBarClustered
    .ChartArea.Height = 107
    .ChartArea.Width = 167
.ChartStyle = 6
End With


ActiveChart.Export "C:\Users\shsy\chart1.jpg"

f = activesheet.Name
Sheets(f).Select
ActiveWindow.SelectedSheets.Visible = False

fname = "C:\Users\shsy\chart1.jpg"
UserForm1.Image1.Picture = LoadPicture(fname)

End Sub

Thanks in advance for your help.

Solution

Please, try the next way. It adds a chart on the active sheet, gives the possibility to play with its dimensions, save its picture and use it to place on the UserForm1.Image1.Picture, then delete the added chart:

Private Sub CommandButton4_Click()
  Dim sh As Worksheet, chartarray1, chartarray2, mychart As chart, fname As String

  chartarray1 = Array(val(UserForm1.TextBox6.Value), val(UserForm1.TextBox7.Value))
  chartarray2 = Array("methane", "carbon")

 Set sh = ActiveSheet
  
 Set mychart = sh.ChartObjects.Add(left:=1, top:=10, width:=300, height:=200).chart 'play here with the chart dimensions
 With mychart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).name = "emissions"
    .SeriesCollection(1).XValues = chartarray2
    .SeriesCollection(1).Values = chartarray1
    .ChartType = xlBarClustered
        .ChartArea.height = 107
        .ChartArea.width = 167
    .ChartStyle = 6
    .Parent.Activate
 End With

 fname = ThisWorkbook.Path & "\chart1.jpg"
 ActiveChart.Export fname

 UserForm1.Image1.Picture = LoadPicture(fname)
 
 'delete the created chart:
 mychart.Parent.Delete
End Sub

It now, saves the picture on ThisWorkbook (keeping this code) path. You can change it as you want.

And it is good to declare all used variables. Such a habit will save you from a lot of troubles in the future...