Add Shapes To Selection Excel VBA Macro

This macro adds a rectangular shape to each cell of the selected range and shows the text of the cell in the shape. It can be useful to quickly convert a particular range of a data set into a formatted label.

Macro/VBA code: 

  Sub AddShapesToSelection()

      Dim tCell As Range, shp As Shape

      Dim x As Integer, y As Integer, w As Integer, h As Integer

      For Each tCell In Selection

          x = tCell.Left + 2

          y = tCell.Top + 2

          w = tCell.Width - 4

          h = tCell.Height - 4

          Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h)

          With shp

              With .TextFrame

                  .Characters.Text = tCell.Value

                  .Characters.Font.Bold = True

                  .HorizontalAlignment = xlHAlignCenter

                  .VerticalAlignment = xlHAlignCenter

              End With

              With .Fill

                  .Solid

                  .ForeColor.RGB = RGB(0, 0, 255)

              End With

          End With

      Next

  End Sub

Macro explained:

• First we declare a Range object variable (tCell) that will hold each cell in the selection, a Shape object variable (shp) to hold the rectangular shape for each cell, along with the coordinates to position the rectangle (x,y,w,h) as integers.
• Then we start a For Each loop through all cells in the selection.
• We get the coordinates of each cell with the Left, Top, Width, and Height properties of the Range object and assign that to the coordinate variables x, y, w, and h, with a small correction of 2 pixels.
• Then we add a rectangular shape with those coordinates so that it fits into the selection. Note that we assign the shape to the object variable shp. The list of available Microsoft auto shapes is available in this other page: AutoShape Type Enumeration
• Next we set the text, alignment, and color properties of the shape using a With statement. Note there are two nested With statements within the main With for the TextFrame and Fill properties respectively.

This is how we add shapes to selections in Excel with VBA macros.

Other examples:

• How to List All Files in Folder with Excel VBA macros
• How to Copy Filtered Data To New Sheets with Excel VBA macros
• How to Remove Unused Rows And Columns with Excel VBA macros