Graph to enlarge when clicked then to go back to normal

[posfog]

Morning/afternoon/evening all

I have created an excel sheet that contains a table full of data which i have added a graph at the bottom of the table (small size) but i would like to be able to click on the graph and for it to open up in a new sheet and view it on a larger scale which the option of that then being able to close down and go back to the first version.

Any help would be greatly received

Regards
P

 

[RickXL]

Hi,

This is a bit "cheap and cheerful" but it should get you started.

I had a worksheet called "Sheet1" with a chart named "Chart 1". Then I created a chart sheet called "Chart1" that had a larger version of the chart in it.

Now you need to create a new Class Module that will permit the use of Events. The only event I use is the Chart Activate event. When the (small) chart is activated it will first select the A1 cell in the worksheet. Without that, the code gets stuck in a loop and when you re-select Sheet1 it returns straight to the large chart because the small chart is still activated.

To get the process started you need to ensure that an instance of the class is created. That is done in the ThisWorkbook module. The Open event is used.

This is the code that goes into the ThisWorkbook module:

Dim clsChartEvent As New Class1

Private Sub Workbook_Open()
    Set clsChartEvent.EventChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
End Sub

This is the code that goes into a new class module:

Public WithEvents EventChart As Chart

Private Sub EventChart_Activate()
    Worksheets("Sheet1").Range("A1").Select
    Charts("Chart1").Activate
End Sub

Note I assumed that the new class module will be called "Class1".

After you have inserted the code, you will need to run the Workbook.Open code. You can do that either in the VB editor or by closing Excel and re-opening it.

I am not sure how much you know about Class modules so I will stop now. If you want a longer explanation about why this works I will be happy to provide one.


Regards,