Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save eli-oat/f99cfb1c0ee4ee5ed47c8c4a5b7d9fd2 to your computer and use it in GitHub Desktop.
Save eli-oat/f99cfb1c0ee4ee5ed47c8c4a5b7d9fd2 to your computer and use it in GitHub Desktop.
Make active Excel chart overlap a range completely
Option Explicit
Public Sub SetActiveChartToCompletelyCoverARange()
Call ResizeAndRepositionChart( _
Chart:=ActiveChart, _
Range:=AskUserToInputARange())
End Sub
Private Sub ResizeAndRepositionChart(ByRef Chart As Excel.Chart, ByRef Range As Excel.Range)
If Chart Is Nothing Then
Exit Sub
End If
If Range Is Nothing Then
Exit Sub
End If
With Chart.Parent
.Height = Range.Height
.Width = Range.Width
.Top = Range.Top
.Left = Range.Left
End With
End Sub
Private Function AskUserToInputARange() As Excel.Range
Dim ReturnValue As Excel.Range
On Error Resume Next
Set ReturnValue = Application.InputBox( _
Title:="Select range", _
Prompt:="Select range for chart to cover", _
Type:=8)
On Error GoTo 0
Set AskUserToInputARange = ReturnValue
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment