Treemap Height and Width from a cell entry

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
433
Office Version
  1. 365
Platform
  1. Windows
I have a treemap and I want the height and width of the treemap to come from a cell entry.
e.g.
The Height is Cell T1 which is 200 (mm)
The Width is Cell Q3 which is 1220 (mm)
Is this possible?
Thanks for your help
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
This macro changes the height and width of the first chart on the active sheet:
VBA Code:
Public Sub Set_Chart_Size()
    With ActiveSheet
        .Shapes(1).Height = Application.CentimetersToPoints(.Range("T1").Value / 10)
        .Shapes(1).Width = Application.CentimetersToPoints(.Range("Q3").Value / 10)
    End With
End Sub
To run the macro automatically when you change one of the cells put this in the sheet's module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$T$1" Or Target.Address = "$Q$3" Then
        Set_Chart_Size
    End If
End Sub
 
Upvote 0
Thank you I will test it when I am home at the weekend and will get back to you.
I appreciate your help.
 
Upvote 0
Just one further question on TreeMaps at this point.
Is there a way of changing the sequence of the TreeMap blocks?
It looks like they start with the largest block and finish on the right with the smallest?
It would be great if I could determine the order of the blocks?
Thanks again for all your help.
 
Upvote 0
It seems not.
 
Upvote 0
Okay thank you for trying.
It's definitely an enhancement that would be nice to have.
Is there a way of asking for future enhancements with Microsoft Excel that you are aware of?
Thanks again for all your time.
I will get back to you on the macro as soon as I have had chance to do it.
Kind Regards
 
Upvote 0
If I wanted to action this on a button would I just select the part in bold to sit between
the Start and end sub?


Public Sub Set_Chart_Size()
With ActiveSheet
.Shapes(1).Height = Application.CentimetersToPoints(.Range("T1").Value / 10)
.Shapes(1).Width = Application.CentimetersToPoints(.Range("Q3").Value / 10)
End With

End Sub

Thanks
Peter
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top