excelvbashapesright-align

Align right edge of shape to left edge of target cell


I have a well working VBA script that when a target cell with the value, "Click to Learn More" is clicked, a corresponding informational pop-up shape is made visible.

My hope is that I'll be able to add code that realigns the shape's right edge to the target cell's left edge, preferrably centered vertically as with the screenshot example. I've tried some suggestions using msoAlignRight, but I'm not enough of a VBA expert yet to know the right syntax -if that's even the right way of doing this.

enter image description here

Thanks for any suggestions!

(The name of the corresponding shape is assigned to the variable, "ImgName", and the "Click for More Information" cell is the Target cell.)


Solution

  • Here is an illustration:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim s As Shape
    
    Set s = ActiveSheet.Shapes("Rectangle 1")
    
    If Target.Address = "$G$10" Then
        With s
            .Visible = True
            .Left = Target.Left - s.Width - 10
            .Top = Target.Top + (Target.Height - s.Height) / 2
        End With
    Else
        s.Visible = False
    End If
    
    End Sub
    

    enter image description here