How to Do Drawing in Excel Using VBA (5 Ideal Examples)

Looking for ways to do drawing using VBA in Excel? Then, this is the right place for you. Sometimes we want to run a macro after some time delay. To do that, you can follow different ways. Here, you will find 5 examples of drawing using VBA in Excel.

How to Do Drawing in Excel Using VBA (5 Ideal Examples)

Here, we will show you examples of drawing shapes, adding text in shapes, modifying fill color & border, changing any shapes already existing in your Excel sheet, and looping through shapes to do a drawing using VBA.

1. Employing VBA AddShape Method to Do Drawing in Excel

In the first example, we will show you how you can add shapes in Excel to do a drawing using VBA. Here are the steps.

Steps:

Firstly, go to the Developer tab >> click on Visual Basic.

Now, the Microsoft Visual Basic for Application box will open.
Then, click on Insert >> select Module.

Next, write the following code in your Module.

Sub Adding_Shapes()
Dim Shape1 As Shape
Dim range1 As Range
  Set range1 = Range("B1:E4")
  Set Shape1 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
            ActiveCell.Left, ActiveCell.Top, range1.Width, range1.Height)
End Sub

Code BreakDown

To start with, we created a Sub Procedure named Adding_Shapes.
Then, we declared Shape1 as Shape and range1 as Range.
After that, we set range1 as cell range B1:E4 and set Shape1 as a Rectangle.

After that, click on the Save button to save the code and go back to your Excel Sheet.

Further, go to the Developer tab >> click on Macros.

Now, the Macros box will appear.
Next, select Adding_shapes.
After that, click on Run.

Finally, a rectangular shape will be added to the worksheet.

Read More: How to Do 3D Drawing in Excel

2. Use TextRange Object to Add Text in Shape for Drawing in Excel

Next, we will show you how you can add text in any shape using VBA to draw in Excel using the TextRange object.

Follow the steps given below to use this object in your Excel file.

Steps:

In the beginning, insert a module going through the same steps shown in Method 1.
Then, write the following code in your Module.

Sub Using_TextRange_Object()
Dim Shape2 As Shape
  Set Shape2 = ActiveSheet.Shapes.AddShape(msoShape16pointStar, _
            ActiveCell.Left, ActiveCell.Top, 200, 150)
  Shape2.TextFrame2.TextRange.Text = "Text in Shape!"
  Shape2.TextFrame2.TextRange.Font.Bold = True
  Shape2.TextFrame2.TextRange.Font.Italic = True
  Shape2.TextFrame2.TextRange.Font.UnderlineStyle = msoUnderlineDottedLine
  Shape2.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(225, 140, 71)
  Shape2.TextFrame2.TextRange.Font.Size = 16
  Shape2.TextFrame.HorizontalAlignment = xlHAlignCenter
  Shape2.TextFrame.VerticalAlignment = xlVAlignCenter
End Sub

Code BreakDown

To start with, we created a Sub Procedure named Using_TextRange_Object.
Now, we declared Shape2 as Shape.
After that, we used the TextRange object to add text and modified text font, color size, etc.

Next, select Cell B4.
Afterward, go to the Developer tab >> click on Macros.

Then, the Macro box will open.
Further, select Using_TextRange_Object.
Lastly, click on Run.

Thus, you can add text into any shape using VBA.

Read More: How to Use Drawing Tools in Excel

3. Modify Fill Color & Border in Shape While Drawing

You can also modify the Fill Color and Border of any shape while drawing using VBA by following some easy steps. Here we will show you how you can add a rounded rectangle and modify its fill color and border.

Steps:

To start with, insert a module going through the same steps shown in Method 1.
After that, write the following code in your Module.

Sub FillColor_Border()
Dim Shape3 As Shape
  Set Shape3 = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, _
            ActiveCell.Left, ActiveCell.Top, 200, 150)
  Shape3.Fill.ForeColor.RGB = RGB(253, 234, 218)
  Shape3.Line.DashStyle = msoLineDashDotDot
  Shape3.Line.ForeColor.RGB = RGB(252, 213, 181)
  Shape3.Line.Weight = 2
  'Shape3.Line.Visible = False
End Sub

Code BreakDown

In the beginning, we created a Sub Procedure named FillColor_Border.
Now, we declared Shape3 as Shape.
Then, we set Shape3 as a Rectangle.
After that, we modified the Fill Color and Border.

Afterward, save the code and go back to your Excel Sheet.
Now, open the Macro box following the steps given in Method 1.
Next, select FillColor_Border.
Lastly, click on Run.

Thus, you can modify Fill Color and Border for any shape using VBA

Read More: Drawing Decision Trees in Excel

4. Change Shape Type to Do Drawing in Excel

Sometimes you may want to change the shape type already existing in your worksheet. You can easily change the shape type using VBA in Excel.

Suppose we have a rectangular shape in our worksheet and now we want to change it to an oval shape. To do that, follow the steps given below.

Steps:

Firstly, select the shape you want to change. In our case, we want to change the Rectangular 5 shape.

Then, insert a module following the same steps given in Method 1.
After that, write the following code in your Module.

Sub Changing_Shape_Type()
Dim Shape4 As Shape
  Set Shape4 = ActiveSheet.Shapes("Rectangle 5")
  Shape4.AutoShapeType = msoShapeOval
End Sub

Code BreakDown

To start with, we created a Sub Procedure named Changing_Shape_Type.
Then, we declared Shape4 as Shape.
Lastly, we change Shape4 from Rectangular to Oval.

Next, save the code and go back to your Excel Sheet.
Now, open the Macro box following the steps given in Method 1.
Afterward, select Changing_Shape_Type.
Finally, click on Run.

Thus, you can change the shape type to do the drawing in Excel using VBA.

5. Loop Through Shapes to Perform Drawing in Excel

In the final example, we will loop through shapes present in a worksheet and change the Fill Color and Border for a specific type of shape while performing drawing in Excel.

Here, we have a rectangular and a star shape in your worksheet. Now, we will show you how you can loop through these shapes and change the Fill Color and Border only for the rectangular shape.

Steps:

Firstly, insert a module going through the same steps given in Method 1.
After that, write the following code in your Module.

Sub Looping_Through_Shapes()
Dim Shape5 As Shape
  For Each Shape5 In ActiveSheet.Shapes
            If Shape5.AutoShapeType = msoShapeRectangle Then
            Shape5.Fill.ForeColor.RGB = RGB(253, 234, 218)
            End If
  Next Shape5
End Sub

Code BreakDown

Firstly, we created a Sub Procedure named Looping_Through_Shapes.
Now, we declared Shape5 as Shape.
Then, we used a for loop for each shape.
After that, we changed the Fill Color for only rectangular shapes.

Next, save the code.
Then, open the Macro box following the steps given in Method 1.
Now, select Looping_Through_Shapes.
Lastly, click on OK.

Finally, you will see that the Fill Color and Border have been modified only for the star shape.

Download Practice Workbook

You can download the workbook to practice yourself.

Drawing Using VBA.xlsm

Conclusion

So, in this article, we have shown you 5 examples of drawing using VBA in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!

<< Go Back to Drawing in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Do Drawing in Excel Using VBA (5 Ideal Examples)

How to Do Drawing in Excel Using VBA (5 Ideal Examples)

1. Employing VBA AddShape Method to Do Drawing in Excel

2. Use TextRange Object to Add Text in Shape for Drawing in Excel

3. Modify Fill Color & Border in Shape While Drawing

4. Change Shape Type to Do Drawing in Excel

5. Loop Through Shapes to Perform Drawing in Excel

Conclusion

Related Articles

Leave a reply Cancel reply