Enlarge a Chart

I have created a dashboard using Pivot charts and have multiple charts all on the same sheet, but they look quite small
What I want to be able to do is click a chart and the chart fills the page, click the chart again and it reverts back to its normal size

Is this possible and how do I go about this

Thanks in advance

Hi shane1966,

See the attached file which has been tested on Excel 2003 and 32 bit Excel 2010. 'Left Click' on a Chart to enlarge. 'Left Click' again to restore the chart to it's original position. Macros must be enabled to run the code.

To port the code to your file:
a. Export module ModEnlargeRestoreCharts from my file. Import it to your file.
b. Run Macro AssignMacroToAllChartsOnActiveSheet() on your file.
c. Save your file.

Complete code follows:

Option Explicit

Private myChartLeft As Double
Private myChartTop As Double
Private myChartWidth As Double
Private myChartHeight As Double


Sub AssignMacroToAllChartsOnActiveSheet()
  'This will assign a Macro to all Charts on the Active Sheet
  
  Dim Sh As Shape
  
  For Each Sh In ActiveSheet.Shapes
    If Sh.Type = msoChart Then
      Sh.OnAction = "EnlargeOrContractShape"
    End If
  Next Sh
  
End Sub

Sub RemoveMacrosFromAllChartsOnActiveSheet()
  'This will  unassign Macros from all Charts on the Active Sheet
  
  Dim Sh As Shape
  
  For Each Sh In ActiveSheet.Shapes
    If Sh.Type = msoChart Then
      Sh.OnAction = ""
    End If
  Next Sh
  
End Sub

Sub EnlargeOrContractShape()
  'This enlarges or contracts a Shape on the 'Active Sheet'
  
  Dim Sh As Shape
  
  Dim dHeight As Double
  Dim dLeft As Double
  Dim dTop As Double
  Dim dWidth As Double
  
  Dim dExcelUsableWidth As Double
  Dim dExcelUsableHeight As Double
  

  Dim sName As String
  
  'Get the name of the Shape that called this routine
  On Error Resume Next
  sName = Application.Caller
  
  'Process only if this routine was activated by a Chart
  If Err.Number <> 0 Then
    On Error GoTo 0
    Exit Sub
  End If
  On Error GoTo 0
  
  Set Sh = ActiveSheet.Shapes(sName)
  
  'Exit if the Shape is NOT a Chart
  If Sh.Type <> msoChart Then
    Exit Sub
  End If
  
  'Get the Excel Window Current Usable Width and Current Usable Height
  dExcelUsableWidth = Application.UsableWidth
  dExcelUsableHeight = Application.UsableHeight
  
  'Get the Chart position attributes
  dLeft = Sh.Left
  dTop = Sh.Top
  dWidth = Sh.Width
  dHeight = Sh.Height
  
  
  If myChartWidth = 0# Then
  
    'Save the current (original) Chart position attributes for later restoration
    myChartLeft = dLeft
    myChartTop = dTop
    myChartWidth = dWidth
    myChartHeight = dHeight
  
    'Resize (Maximize) the Chart
    Sh.Left = 5
    Sh.Top = 5
    Sh.Width = dExcelUsableWidth - 40
    Sh.Height = dExcelUsableHeight - 40
    
    'Move the Shape to the Front
    Sh.ZOrder msoBringToFront
    
  Else
  
    'Resize the Shape to it's original position
    Sh.Left = myChartLeft
    Sh.Top = myChartTop
    Sh.Width = myChartWidth
    Sh.Height = myChartHeight
    
    'Reset the sentinel that will allow the chart to be maximized again
    myChartWidth = 0#
    
  End If

End Sub

To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
http://office.microsoft.com/en-us/tr...001150634.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

To import or export VBA code:
a. To export, right click on the Module Name in the 'Project Explorer'.
b. Select export file. I suggest you use a SubFolder that only contains exported (.bas) files.
Keep the original name.
c. To import, right click anywhere in 'Project Explorer'.
d. Select import file. Select a file to import.

Lewis