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

Question

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.

I can't test but if this is all your code, the `charts.add` line(s) might be causing it? The one at the top of the sub probably isn't needed. — NickSlash, Mar 08 '23 at 13:34
That's right, the `charts.add` was indeed useless but the problem didnt solve by deleting it. — shahin syr, Mar 08 '23 at 13:41
I would suggest you to not use a chart sheet, to create a chart **on the active sheet**, export it as picture, use it, then delete... — FaneDuru, Mar 08 '23 at 14:06
@FaneDuru, I also had this idea. Now I export the picture (like before) and added one line `ActiveWorkbook.Charts.Delete`. Now the newly generated sheets are shot down the moment that I run the code and hence, before showing the graph I get the message : "the sheet will be permanently deleted, do you want to delete?" and I have to click yes for this question every time that I run the code. — shahin syr, Mar 08 '23 at 14:38
You also use a chart sheet... Anyhow, if you like this way, you should place the code line you show between `Application.DisplayAlerts = False` and `Application.DisplayAlerts = True`. Now, it should be deleted without any warning. — FaneDuru, Mar 08 '23 at 14:43
@FaneDuru Thanks a lot for your help. That worked perfectly fine, and i also deleted the lines `f = activesheet.Name Sheets(f).Select ActiveWindow.SelectedSheets.Visible = False` and got rid of the extra worksheet generating. So i am going to update my post and leave only the question of resizing . — shahin syr, Mar 08 '23 at 18:28
@FaneDuru Could you please tell me how to edit my code to have the chart on the active sheet? I think then I can unlock the shape easier. Thanks — shahin syr, Mar 09 '23 at 08:19
I am not in my office. I will show you how to do it after about 2 hours, when I will be there. — FaneDuru, Mar 09 '23 at 08:43
It might be more reliable to use `.Parent.Height` and `.Parent.Height` to resize the chart. — Jon Peltier, Mar 09 '23 at 22:22

score 1 · Accepted Answer · answered Mar 09 '23 at 13:13

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

Many thanks @faneduru for your time. This code solved all my problems with the chart. Very much appreciated. — shahin syr, Mar 09 '23 at 14:38

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

1 Answers1