Excel Join The Dots

There are many different types of shapes in Excel. This post shows how to design diagrams to join the dots and solve it adding Excel free-forms programmatically. We can use it to design a pattern of numbers to create a custom shape or play to join the dots. The basics of adding and manipulating shapes with Excel VBA are covered in this other page: Shape Object. In this post we focus on the free-form shape.

Adding the dots

We use a Worksheet SelectionChange event to add numbers, one after another, upon selecting a cell. When the selected cell falls within the drawing board (range B2:AX26 in this example), it uses the function AddNumber to add the number if empty, or remove the number if not empty. It also loops through shapes to check if a free form has already been created.

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  

  If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
 
      'considers B2:AX26 the drawing board or range
      If Not Application.Intersect(Range("B2:AX26"), Target) Is Nothing Then
   
          'loop through shapes in the sheet
          For Each shp In Shapes
              'this prevents adding numbers if free form was already created
              If shp.Type = msoFreeform Then Exit Sub
          Next shp
   
          'add or remove numbers
          If Target.Value = "" Then
              Target.Value = AddNumber
          Else
              Target.Value = ""
          End If
      End If
  End If
  

  End Sub
 

The function AddNumber checks the maximum number in the drawing range and adds the next number in the selected cell. The range has previously been formatted to center the numbers horizontally and vertically. The numbers themselves represent the dots in the game.

 

Finding the dots

We find the dots or numbers using the Find method of the Range object representing the drawing board, and return the row and column of the cell with each number. For the first dot, the code looks like the following.

  Dim boardRng As Range, r As Integer, c As Integer
  Set boardRng = Range("B2:AX26")
  r = boardRng.Find(What:=1, LookAt:=xlWhole).Row
  c = boardRng.Find(What:=1, LookAt:=xlWhole).Column
 

Then we can get the position of that cell to add the node of the free form (see next). We do the same for the rest of the dots but now putting that code into a loop from the second dot until the last one (max value) as indicated below.

  Dim maxn As Integer, n As Integer

  maxn = WorksheetFunction.Max(boardRng)
  For n = 2 To maxn
      r = boardRng.Find(What:=n, LookAt:=xlWhole).Row
      c = boardRng.Find(What:=n, LookAt:=xlWhole).Column
      '...
  Next n
 

Then we get the center of each cell containing a number as the starting position to add the free form node. The code below gets the starting horizontal and vertical position, which is the center of the cell containing number 1.

  Dim startx As Single, starty As Single  

  startx = Cells(r, c).Left + Cells(r, c).Width / 2
  starty = Cells(r, c).Top + Cells(r, c).Height / 2

  

Adding the free-form shape

Adding a free-form is slightly different than adding other shapes. The method of the Shape object used is BuildFreeform, instead of AddShape, AddTextBox, etc, and returns a FreeFormBuilder object. It accepts three parameters along with methods to add nodes. The parameters include the free-form editing type, the row and column position of the first node. The editing type can be set to auto, corner, smooth curve, or symmetric curve. The row and column position coordinates are given in points relative to the top-left corner of the worksheet. 

  ActiveSheet.Shapes.BuildFreeform (msoEditingAuto, startX, startY)

We add each additional node using the AddNodes method, which accepts the same parameters and another one to determine the type of connecting line or segment (either line or curve). The code below adds a node with a line segment (we can make it curved with msoSegmentCurve).

    
  Dim x As Single, y As Single
  x = Cells(r, c).Left + Cells(r, c).Width / 2
  y = Cells(r, c).Top + Cells(r, c).Height / 2  
  .AddNodes msoSegmentLine, msoEditingAuto, x, y

If we want to close the free form shape we need to add a final node segment connected with the starting point. That would come after the loop with a similar code line having the starting position coordinates. Finally, we need to convert it to a shape so that the free form becomes visible and part of the Shapes collection in that worksheet.

  

  .AddNodes msoSegmentLine, msoEditingAuto, startx, starty
  .ConvertToShape

 

The free form shape is filled by default. But we may rather want to see the lines only (depending on the drawing). In that case, we should assign the shape to an object variable (shp) and make the fill color not visible as indicated below.

  

  Set shp = .ConvertToShape
  shp.Fill.Visible = msoFalse

 

We can use this example to play to join the dots or create similar games. We can also use it to draw free-form shapes to create designs or figures/characters for Excel games or templates.

 

Download Excel Join The Dots