In this article you will learn a trick to resize and relocate a chart using VBA. We have created a shortcut key Shift+Ctrl+R to resize and relocate active chart. You just need to select the range and your selected chart will be relocated and resized according to selected range. You can move the chart to another worksheet also.
Below is the VBA Code-
Sub Resize_Chart()
Dim cht As Chart
On Error Resume Next
Set cht = ActiveChart
On Error GoTo 0
If cht Is Nothing Then
MsgBox "Please select the chart"
Exit Sub
End If
Dim rng As Range
Set rng = Application.InputBox("Please select the Range", "Range Selection", Type:=8)
'Chart Object is the parent of Chart
cht.Parent.Top = rng.Top
cht.Parent.Left = rng.Left
cht.Parent.Height = rng.Height
cht.Parent.Width = rng.Width
cht.Location xlLocationAsObject, rng.Parent.Name 'Worksheet name is the parent of range
End Sub
Just copy this code and paste in VBA Module. You add the shortcut key to run this macro. To add a shortcut key
- Go to View Tab >>Macros>>View Macros>>
- Select Resize Chart Macro and Click on Options button
- Put capital “R” in shortcut key box.
Click here to download this Excel workbook.
