Centering Image in Merged Range

Hi again Simon,

Dang it all! That's what happens when you test your code on a "nice little workbook" instead of on a "real" one!

The attached workbook contains a new version of the "CheckShapeAndRange" routine (shown below), and appears to do what you need.

Sub CheckShapeAndRange()

    Dim rTargetRange    As Range
    Dim bShapeFound     As Boolean
    Dim rCell           As Range
    Dim shp             As Shape

    If TypeName(Selection) = "Range" Then

          Set rTargetRange = Selection

          For Each shp In ActiveSheet.Shapes

              bShapeFound = False

              For Each rCell In rTargetRange.Cells

                  If rCell.Address = shp.TopLeftCell.Address Then
                      bShapeFound = True
                      Exit For
                  End If

              Next rCell

              If bShapeFound = True Then

                    Call CentreShape(rTargetRange:=rTargetRange, shp:=shp)
                    Exit For

              End If

          Next shp

          If bShapeFound = False Then
              MsgBox "The selected cells do not contain a Shape", vbExclamation
          End If

    Else: MsgBox "Select a range before using this feature", vbExclamation

    End If

End Sub

Regarding:

but I'm curious why you included it in the first place?

I normally include the "Option Private Module" statement to prevent macros being made available to "curious Users" via the Macro menu - I prefer to make macros available via CommandButtons on a worksheet or icons on the Ribbon.


Hope the above helps - as always, please let me know how you get on.

Regards,

Greg M