How to Resize Chart Plot Area Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

You can create a chart through Excel’s built-in chart features. While dragging the chart to any of the sides you can change the size and shape of the chart. But when you need to resize the chart to a fixed height and width. VBA macros provided different commands to fix the height and width. You can resize the chart area without changing the plot area. Besides, you can change both the chart and plot area using the VBA code. In this article, we are going to show you the steps to resize the chart plot area by VBA in Excel. So, let’s get started.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


3 Steps to Resize Chart and Plot Area with VBA in Excel

We have demonstrated 3 easy and straightforward ways to resize chart and plot area with VBA in Excel.

Not to mention, we have used the Microsoft Excel 365 version. You may use any other version at your convenience.


Step 01: Populate Dataset with Necessary Components

  • Firstly, you need to populate your dataset with the necessary components. We have taken a dataset of Month-wise Sales for 2 years. With this dataset, we will plot a chart now.

Populate Dataset with Necessary Components to resize chart plot area using VBA in Excel

Read More: How to Resize Chart Area Without Resizing Plot Area in Excel


Step 02: Insert a Chart

  • Eventually, select the entire range of the dataset.
  • Then, navigate to the Insert tab >> choose Insert Column or Bar Chart >> Clustered Column.

Insert a Chart to resize chart plot area using vba in Excel

Finally, you will get a chart like the image below. Now, we will resize this chart with the VBA code.

Read More: How to Create an Area Chart in Excel (6 Suitable Examples)


Similar Readings


Step 03: Apply VBA Code to Resize Chart Plot Area

  • At this moment, we will insert a VBA code to resize the chart plot area.
  • Initially, select the chart area and hover over the Developer tab >> pick Visual Basic.

Apply VBA Code to Resize Chart Plot Area in Excel

Note: you can open the Module by pressing the ALT + F11 key.

  • Consequently, choose the Insert tab >> Module>> Module1.

  • Sequentially, write up the following code in Module 1.
Sub Resize_Chart_Plot_Area()
  Dim ch1 As Chart, plot_height As Double, plot_width As Double
  Set ch1 = ActiveChart
  plot_height = ch1.PlotArea.Height: plot_width = ch1.PlotArea.Width
  ch1.Parent.Height = 225: ch1.Parent.Width = 550
  ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 500
End Sub

Code Breakdown:

  • Firstly, we created a Sub Procedure named Resize_Chart_Plot_Area.
  • Secondly, we declared ch1 as Chart and plot_height and plot_width as Double.
  • After that, we set ch1 as ActiveChart.
  • Next, we set plot_height to ch1.PlotArea.Height and plot_width to ch1.PlotArea.Width. We replaced the variable value with 150 and 500 to Plot Area height and width respectively.
  • Then, we changed the chart height and width also to 225 and 550 using the Parent.Height and Parent.Width commands.
  • Finally, we stop the code with the End Sub command.

VBA code to resize chart plot area in Excel

Lastly, run the code with the F5 key and your chart will be automatically resized like ours.

Read More: Excel Area Chart Data Label & Position


How to Move Chart to Specific Cell with VBA in Excel

You can set the chart in a specific cell by using the VBA code also. It means you selected the cell range and the chart will be relocated to that specific cell range.

For doing this, we will open a new module as we mentioned earlier. Then, write up the following code in that Module.

Sub Relocate_Chart()
Dim ch1 As Chart
On Error Resume Next
    Set ch1 = ActiveChart
On Error GoTo 0
If ch1 Is Nothing Then
    MsgBox "Select the Chart First"
    Exit Sub
End If
Dim r As Range
Set r = Application.InputBox("Select the Range", "Range Selection", Type:=8)
ch1.Parent.Top = r.Top
ch1.Parent.Left = r.Left
ch1.Parent.Height = r.Height
ch1.Parent.Width = r.Width
ch1.Location xlLocationAsObject, r.Parent.Name
End Sub

Code Breakdown:

  • Firstly, we created a Sub Procedure named Relocate_Chart.
  • Then, we created a MsgBox. It will be displayed when you don’t select the chart. It will show you a message “Select the Chart First”.
  • Eventually, we set r as Range and insert an InputBox command for selecting the range.
  • Finally, we fixed the chart to the selected range box.

How to Move Chart to Specific Cell with VBA in Excel

At this moment, You can add a button utilizing the Form Controls in your worksheet. We have named the button Relocate.

  • Now, right-click on the button and choose Assign Macro from the Context Menu.

Assign macro to resize chart plot area using vba in excel

  • Sequentially, the Assign Macro window appears. Select the Macro name to Relocate_Chart.
  • Lastly, hit OK.

Finally, after clicking the Relocate button, select the range where you want to put the chart and the chart will take place there. See the below GIF for better visualization.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy steps to resize the chart and plot area with VBA in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

2 Comments
  1. Hi Sir,
    Have a good day.
    If i want to resize the plot area but my chart area need to be fixed. Can it be done? If yws, then what modification should be needed?

    • Reply Avatar photo
      Fahim Shahriyar Dipto Jan 11, 2023 at 2:00 PM

      Hello Terry,
      It’s really nice to hear from you. In your query, you wanted to know about changing the plot area where the chart area will be the same. The VBA code needs to be modified a bit. I have attached the code below.
      Sub Resize_Chart_Plot_Area()
      Dim ch1 As Chart, plot_height As Double, plot_width As Double
      Set ch1 = ActiveChart
      chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
      chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
      ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 400
      End Sub
      Run the code with the F5 key and it will change the plot area without changing the plot area.
      Have a great day.
      Regards,
      Fahim Shahriyar Dipto
      Excel & VBA Content Developer.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo