CODEWITHSUNDEEP
excelvbashapes
Gokhan
Gokhan

Reputation: 497

is there select shape event in VBA?

I have excel Project that includes pictures. I have userform that has ImageBox. This form shows pictures dynamically according to row number with using selection change event.

This event triggered when cell selection change. But i want to triggered this event by clicking on shape. Is there any solution for that?

Please see image. enter image description here

These are codes for cell selection change event.

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False
Dim Pic As Shape
  For Each Pic In ActiveSheet.Shapes
    If Pic.TopLeftCell.Row = ActiveCell.Row Then
      If Pic.Type = msoPicture Then
         Pic.Select
    
         Dim sheetName As String, MyPicture As String, strPicPath As String
         sheetName = ActiveSheet.Name
         MyPicture = Selection.Name

         strPicPath = SavedPictureTo(MyPicture)' This function save image
         Load ImageViever      'ImageViewer is UserForm name

          With ImageViever
          .Image1.Picture = LoadPicture(strPicPath)
          .Show vbModeless
         End With
        Exit For
      End If
    End If
  Next

Application.ScreenUpdating = True
End Sub

Upvotes: 2

Views: 5664

Answers (1)

FunThomas
FunThomas

Reputation: 29146

As written in the comments, you can assign a (parameterless) Sub to a shape that is executed when a shape is clicked.

In Excel, you can assign the macro by right-clicking on the shape and select "Assign macro".

With VBA, you write the name of the macro to the OnAction-property of the shape:

Dim sh As Shape
For Each sh In ActiveSheet.Shapes  ' <-- Select the sheet(s) you need
    sh.OnAction = "HelloWorld"     ' <-- Change this to the name of your event procedure
Next

If you want to know which shape was clicked, you can use the Application.Caller property. When the macro was called by clicking on a shape, it contains the name of that shape.

Sub helloWorld()
    Dim sh As Shape
    On Error Resume Next
    Set sh = ActiveSheet.Shapes(Application.Caller)
    On Error GoTo 0
    
    If sh Is Nothing Then
        MsgBox "I was not called by clicking on a shape"
    Else
        MsgBox "Someone clicked on " & sh.Name
    End If
End Sub

Upvotes: 7

Related Questions