Graph to enlarge when clicked then to go back to normal ??

[posfog]

Morning All,
I have a table with data showing on it and a small graph underneath it showing what the table is above. Due to size restrictions on this i am unable to show the graph in a decent size so wondered how i can get the graph to go full size when "Clicked" and for it to go back to normal when "Clicked" again.

Any advice would be greatly received

Regards
P

 

[pgc01]

Hi

You can right-click on the chart and assign a macro to toggle between the 2 sizes.

 

[posfog]

Hi

You can right-click on the chart and assign a macro to toggle between the 2 sizes.

Thanks PGc01 would you know what the macro would be for that please?

 

[pgc01]

This is a very simple example that you can adapt.

Let's assume that you have just 1 chart in worksheet Sheet1.

Right-click on the chart and assign it this marcro:

Sub MyChart_Click()
Static b As Boolean
Dim chto As ChartObject

Set chto = Worksheets("Sheet1").ChartObjects(1)

If b Then
    chto.Width = 300
    chto.Height = 200
Else
    chto.Width = 600
    chto.Height = 400
End If
b = Not b
End Sub

Now click several times on the chart and see it changing between 2 sizes.

 

[posfog]

Thanks

 

[pgc01]

You're welcome.

 

[posfog]

Hi All,
The coding below given to me by PGC works great but what would you need to add to the coding if i wanted for example for the top left hand corner of the chart to be on C10 as currently the chart only expands from where the chart is displayed.

Regards
P

This is a very simple example that you can adapt.

Let's assume that you have just 1 chart in worksheet Sheet1.

Right-click on the chart and assign it this marcro:

Sub MyChart_Click()
Static b As Boolean
Dim chto As ChartObject

Set chto = Worksheets("Sheet1").ChartObjects(1)

If b Then
    chto.Width = 300
    chto.Height = 200
Else
    chto.Width = 600
    chto.Height = 400
End If
b = Not b
End Sub

Now click several times on the chart and see it changing between 2 sizes.

 

[pgc01]

Hi

Let's say that for the small size you want the top left corner to be on F15 and for the big size on C10.

Try:

Sub MyChart_Click()
Dim chto As ChartObject
Dim rSmall As Range, rBig As Range
Static b As Boolean

Set chto = Worksheets("Sheet1").ChartObjects(1)
Set rSmall = Worksheets("Sheet1").Range("F15")
Set rBig = Worksheets("Sheet1").Range("C10")

If b Then
    chto.Width = 300
    chto.Height = 200
    chto.Left = rSmall.Left
    chto.Top = rSmall.Top
Else
    chto.Width = 600
    chto.Height = 400
    chto.Left = rBig.Left
    chto.Top = rBig.Top
End If
b = Not b
End Sub

 

[posfog]

Thanks PGC for the below information!
I just wondered when it comes to the height and width how do i work out the sizes so the chart fits perfectly into a certain number of cells?

Hi

Let's say that for the small size you want the top left corner to be on F15 and for the big size on C10.

Try:

Sub MyChart_Click()
Dim chto As ChartObject
Dim rSmall As Range, rBig As Range
Static b As Boolean

Set chto = Worksheets("Sheet1").ChartObjects(1)
Set rSmall = Worksheets("Sheet1").Range("F15")
Set rBig = Worksheets("Sheet1").Range("C10")

If b Then
    chto.Width = 300
    chto.Height = 200
    chto.Left = rSmall.Left
    chto.Top = rSmall.Top
Else
    chto.Width = 600
    chto.Height = 400
    chto.Left = rBig.Left
    chto.Top = rBig.Top
End If
b = Not b
End Sub

 

[pgc01]

Hi

You can use the size information of the range object:

Sub MyChart_Click()
Dim ws As Worksheet
Dim chto As ChartObject
Dim rSmall As Range, rBig As Range
Static b As Boolean

Set chto = Worksheets("Sheet1").ChartObjects(1)
Set rSmall = Worksheets("Sheet1").Range("E10:J20")
Set rBig = Worksheets("Sheet1").Range("C10:M25")

If b Then
    chto.Width = rSmall.Width
    chto.Height = rSmall.Height
    chto.Left = rSmall.Left
    chto.Top = rSmall.Top
Else
    chto.Width = rBig.Width
    chto.Height = rBig.Height
    chto.Left = rBig.Left
    chto.Top = rBig.Top
End If
b = Not b
End Sub