Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Shapes

We often need to use shapes in Excel worksheets. You can insert various kinds of shapes to Excel worksheets from insert menu. Also you can use your own pictures as shapes. Here are few VBA examples related to shapes.

If you want to assign macro programmatically to your shape you can use below method.

Sub AssignMacro()

ActiveSheet.Shapes("Picture 20").OnAction = "'" & ActiveWorkbook.Name & "'!Macro1"

End Sub

You may want to know the row number of cell of top left of your shape. You can use below code for that.

Sub TopLeftCellRowNumber()

MsgBox ActiveSheet.Shapes("Picture 20").TopLeftCell.Row

End Sub

Below code will select all your shapes which have "picture" in its name.

It will select one shape at a time. Then will wait for 1 second and will select next shape.


Sub SelectPictureShapes()

Dim sh As Shape

    For Each sh In ActiveSheet.Shapes

        If InStr(1, sh.Name, "picture", vbTextCompare) > 0 Then

            sh.Select

            Application.Wait Now + TimeValue("00:00:01")

        End If

Next

End Sub



Sometimes you may need to know the name of the clicked shape in your program. Because sometimes your program will need to do different things depend on what shape is clicked. So below code will show the name of clicked shape in a message box. You can develop this to suit with your needs.


msgbox ActiveSheet.Shapes(Application.Caller).Name

Contact Form

Name

Email *

Message *

Popular Posts