Excel Mind Maps

There are many tools to create mind maps available online. But guess what, we can also create mind maps in Excel using this simple macro. The macro adds a shape of the size of the selected range whenever some text is added to a cell/range. The following shapes are linked to the root shape, which is either the first one added or any other shape that has been set as root.

Add Shape

We can add a shape that fits the selected range by getting the position and dimension of the selection as indicated below. In this case, we are adding an oval shape, but that can be changed to any other auto shape. See the full list of auto shapes for Microsoft Office in this other page: Auto Shape Type Enumeration

 With Selection
     x = .Left
     y = .Top
    w = .Width
    h = .Height
 End With
   
 Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, x, y, w, h)
 

The next step is adding the text to the shape and applying any other required formatting. We do that targeting the text frame of the shape along relevant properties. For example, the code below shows how to add text (whatever is stored in cellText), set the font size, and align the content horizontally and vertically.

 With shp
     .TextFrame.Characters.Text = cellText
     .TextFrame.Characters.Font.Size = WorksheetFunction.RoundUp(w / Len(cellText), 0)
     .TextFrame.HorizontalAlignment = xlHAlignCenter
     .TextFrame.VerticalAlignment = xlVAlignCenter
 End With

 

 

It is important to note that the shape is added upon changing the value of a cell/range. That’s captured with the worksheet change event and needs to be programmed within the corresponding sheet module. Below is the event procedure added to the module Sheet1 that calls another macro to add a shape when the cell/range value changes. Target refers to the range object where the value has changed.

 Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Value <> "" Then Call AddShape(Target)
 End Sub

 

Add Connectors

Each new shape is connected to the root shape. The root shape is identified by adding “_root” after its name. There is a condition to do so when the first shape is added. A button triggers another macro to set as root any other shape when selected (see next).

Here’s how we set connectors with Excel VBA. We add the connector like any other shape, just using the corresponding method (AddConnector). Then we specify the type of connector and the position and dimension of the connector.

 Set conn = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 10, 10, 10, 10)

 

Note that we are using a straight-line connector, but the method accepts four different types of connectors:

• Curved connector (msoConnectorCurve)
• Elbow connector (msoConnectorElbow) 
• Straight line connector (msoConnectorStraight)
• A combination of other states (msoConnectorTypeMixed)

The initial position and dimension (10 points for all) is not important because we are going to format those properties in order to link the two shapes. For that, we assign each shape to an object variable that represents either the origin or the destination shape. For example, we could call those variables fromBox and toBox.

 Set fromBox = ActiveSheet.Shapes ("Oval 1_root")
 Set toBox = ActiveSheet.Shapes ("Oval 2")

Then we format the connector (conn) so that it repositions and resizes to link the two shapes. The property ConnectorFormat accepts properties that indicate the begin and end connecting shapes along a parameter that determines the connecting position in the shape. For an oval shape, there are eight possible connecting points for each of the positions shown below.

However, in this case we just use an arbitrary position (e.g. position 1) because another function will automatically update the connecting points to reach the minimum distance between the two. For example, regardless of setting both begin and end connect to the top of the oval shape (value of 1), the function RerouteConnections will then change the connecting point to go from bottom of the shape above to top of the shape below or left to right if placed horizontally. The code to set and reroute the connectors is shown below.

 With conn
     .ConnectorFormat.BeginConnect fromBox, 1
     .ConnectorFormat.EndConnect toBox, 1
     .RerouteConnections
 End With

 

Change Root

In order to continue with sub-levels in the hierarchy of the mind map, we need to keep changing the root shape as needed. Once changed, the following shapes representing other ideas/concepts will link to that other root shape, thus creating a tree of shapes that are inter-linked with each other within the hierarchy.

As explained earlier, the root shape is identified with the text “_root” after the name of the shape. We just need to change that to the new root. The new root is set to whatever new shape is selected. Here’s how we see which shape is selected in a worksheet with Excel VBA.

 If TypeName(Selection) = "Oval" Then MsgBox "Selection is Oval Shape"

 

However, since we can choose to use any other shape we could rather just check the selection is not a range object.

 If TypeName(Selection) <> "Range" Then …

 

Then we remove the text “_root” from the previous root and add it after the name of the selected shape. We can find which shape was the previous root by looping through each shape in the worksheet and check if the name contains the text “_root”.

 For Each shp In ActiveSheet.Shapes
     If InStr(shp.Name, "root") > 0 Then RootShapeName = shp.Name
 Next shp

Then we remove the root identifier from the previous root shape using the string functions Left and Len as follows. That’s simply removing the last five characters.

 ActiveSheet.Shapes(shp.Name).Name = Left(shp.Name, (Len(shp.Name) - 5))

 

Finally, we add the root identifier to the selected shape simply by updating the name as indicated below. Note that we should check whether the selected shape was the root shape to avoid updating the names in such case.

 ActiveSheet.Shapes(Selection.Name).Name =  ActiveSheet.Shapes(Selection.Name).Name & "_root"

 

 Click Here to download Excel Mind Maps.