VBA - Identifying Image Selected

[SerenityNetworks]

I'll use a macro to display several images in a worksheet. Then I'll enter some data in worksheet cells. After that I'll trigger another macro to delete the current images and then trigger the first macro to display a new set of images. All of this works fine.

What I'd like to do now is click on an image and be able to identify the image that was clicked. I'm not a coder, so I'm at a loss how to accomplish this task.

When I use the record macro feature, I record code like the following when I click on an image.

ActiveSheet.Shapes.Range(Array("Picture 39045")).Select

How can I click on an image and store the image selected as a variable (so I can manipulate the image in other procedures)?

Thanks in advance,
Andrew

 

[Yongle]

Like this

Sub ManipulateImage()
    Dim shp As Shape
    Set shp = GetShape
    
    If Not shp Is Nothing Then
        MsgBox "Height = " & shp.Height & vbCr & "Width = " & shp.Width, vbOKOnly, shp.Name
        If MsgBox("DELETE" & vbTab & GetShape.Name, vbYesNo, "Are you sure ?") = vbYes Then GetShape.Delete
    End If
End Sub

Function GetShape() As Shape
    If TypeName(Selection) = "Picture" Then Set GetShape = Application.Selection.ShapeRange.Item(1)
End Function

 

[SerenityNetworks]

Thank you Yongle! This is great and totally workable.

I've since tried to do similar and triggering the event by simply clicking on one of the pictures. That is, something similar to the following, but triggered when a picture is clicked upon.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("A5").Address Then
       MsgBox "A5 was selected"
    End If
End Sub

I've done some prowling of the internet, but not found any method for determining if a picture is selected. Is this possible? So far, I can only see how to use "SelectionChange" with cells.

If it's possible, I'd love to learn how. If it's not possible then your solution will work just fine for what I need.

Thank you again,
Andrew

 

[Jaafar Tribak]

Put the code in the ThisWorkbook Module:

Option Explicit

Private WithEvents cmbrs As CommandBars

Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
End Sub

Private Sub cmbrs_OnUpdate()
    Call ManipulateImage
End Sub

Private Sub ManipulateImage()
    Dim shp As Shape
    Set shp = GetShape
    
    If Not shp Is Nothing Then
        MsgBox "Height = " & shp.Height & vbCr & "Width = " & shp.Width, vbOKOnly, shp.Name
        If MsgBox("DELETE" & vbTab & GetShape.Name, vbYesNo, "Are you sure ?") = vbYes Then GetShape.Delete
    End If
End Sub

Private Function GetShape() As Shape
    If TypeName(Selection) = "Picture" Then Set GetShape = Application.Selection.ShapeRange.Item(1)
End Function

 

[Yongle]

@Jaafar Tribak
Elegant

 

[SerenityNetworks]

@Jaafar Tribak
@Yongle

Thank you Jaafar and thank you too Yongle. With these two routines I have ultimate flexibility.

With the latest routine, the "WithEvents cmbrs As CommandBars" was initially returning "Compile error: Invalid attribute in Sub or Function". But after I moved the "Private Sub Workbook_Open()" routine I am using below your "WithEvents" routine, it all worked perfectly.

Now I just need to substitute in my commands and I'm good.

Thank you again,
Andrew

 

[SerenityNetworks]

Okay. I'm stumped again.

I've successfully implemented the code that moves the picture clicked upon to a defined location and resizes it.

But the user may want to click on the picture again to move it, manually resize it, delete it, etc. (They may or may not activate other cells before clicking on the picture again.) So I'm trying to capture if the picture clicked on is the same picture that was last clicked on. If it is then I want to exit the macro (so the user can move it, resize it, delete it, etc.).

I can set the variable prevName the same as GetShape.Name, but not the variable shpName. I receive a run-time error '91' (see below). I can't figure out how to get around this problem. Any help would be appreciated.

Thanks again,
Andrew

Private Sub ManipulateImage()
    Dim shp As shape
    Dim shpName As Variant
    Dim prevName As Variant
    Dim wActiveCell As String
    Set shp = GetShape
    Set shpName = GetShape.Name ' THIS RETURNS: "Run-time error '91': Object variable or With block variable not set"
    If Not shp Is Nothing Then
        If prevName = "" Then
            prevName = GetShape.Name
            GoTo ResumeProcess
        End If
        If shpName = prevName Then
            Exit Sub
        End If
ResumeProcess:
        wActiveCell = ActiveCell.Address
        Selection.ShapeRange.ZOrder msoBringToFront
        shp.Width = Sheets("Parameters").Cells(27, 2).Value
        shp.Top = Sheets("Parameters").Cells(5, 2).Value
        shp.Left = Sheets("Parameters").Cells(6, 2).Value
        Range(wActiveCell).Select
        'MsgBox "Height = " & shp.Height & vbCr & "Width = " & shp.Width, vbOKOnly, shp.Name
        'If MsgBox("DELETE" & vbTab & GetShape.Name, vbYesNo, "Are you sure ?") = vbYes Then GetShape.delete
    End If
End Sub

 

[Jaafar Tribak]

Try this :

Option Explicit

Private WithEvents cmbrs As CommandBars


Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
End Sub

Private Sub cmbrs_OnUpdate()
    Call ManipulateImage
End Sub


Private Sub ManipulateImage()

    Dim shp As Shape
    
    Set shp = GetShape
    If Not shp Is Nothing Then
        MsgBox "Height = " & shp.Height & vbCr & "Width = " & shp.Width, vbOKOnly, shp.Name
        If MsgBox("DELETE" & vbTab & shp.Name, vbYesNo, "Are you sure ?") = vbYes Then shp.Delete
    End If

End Sub


Private Function GetShape() As Shape

    Static oPreviousShp As Shape
    Dim oCurrentShp As Shape
    
    If TypeName(Selection) = "Picture" Then
        Set oCurrentShp = Application.Selection.ShapeRange.Item(1)
        If oPreviousShp Is oCurrentShp Then
            Set GetShape = Nothing
        Else
            Set oPreviousShp = oCurrentShp
            Set GetShape = oCurrentShp
        End If
    Else
        Set oPreviousShp = Nothing
    End If
    
End Function

 

[SerenityNetworks]

@Jaafar Tribak

Thank you! That did it.

I had to remove the last Else statement.

Else
  Set oPreviousShp = Nothing

Something (the watcher?) was triggering it repeatedly, so it was always setting oPreviousShp to nothing and when I'd click on the same image it would act as though I'd never clicked on it before. After removing the last Else statement, it worked perfectly.

Thank you so very much. I both learned a little more about coding and my tool will be much more convenient for the user.

Regards,
Andrew