How to get the .Name of a Picture/Shape was behind another Picture/Shape

N

NJR

New member
Joined
Jun 26, 2014
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2010
Hi,

I have two Pictures I've inserted into a workbook - one Picture is directly on top of the other.

I assign a Macro to the top Picture - which, when selected, calls "Sub Check" and moves that Picture to the back of the ZOrder - making the bottom Picture now visible.

I then want to get:
- The .Name of the Picture that had been on top and is now moved to the Back <-- This works
- The Cell where the two Pictures are located <-- This works
- The .Name of the Picture that was at the Back and is now at the Front <-- Stuck Here

The code I've used is below - but I'm at a loss as to a solution to get that .Name.

Any suggestions are welcome. (I also attached a sample of the code working so far - if that helps.)

Code: 
Option Explicit
Dim strTheCellSelected As String 'To track the Cell of the Picture that was selected.
Dim shCoverPicture As Shape 'The Cover Picture that has been selected.
Dim varUserSelection As Variant 'What has been selected on the screen.

Sub Check()

    'Determine which Cover Picture was selected and Move it to the Back so the underlying Picture is showing.
    ActiveSheet.Shapes(Application.Caller).Select
    Selection.ShapeRange.ZOrder msoSendToBack

    'Pull-in what is selected on screen
    Set varUserSelection = ActiveWindow.Selection

    'Get the Name of the first Cover Picture selected.
    Set shCoverPicture = ActiveSheet.Shapes(varUserSelection.Name)
    MsgBox ("shCoverPicture.Name = " & shCoverPicture.Name)
    
    'Get the address of the selected Cell
    strTheCellSelected = shCoverPicture.TopLeftCell.Address
    MsgBox ("strTheCellSelected = " & strTheCellSelected)
    
    '***** Stuck Here *****
    'Get the .Name of the Picture/Shape that is undernearth the shCoverPicture that has been selected by the User.
    
        
End Sub
 

Attachments

  • MySheet.xlsm
    20.4 KB · Views: 10
try:
Code: 
Sub Check()

'Determine which Cover Picture was selected and Move it to the Back so the underlying Picture is showing.
Set shCoverPicture = ActiveSheet.Shapes(Application.Caller)
shCoverPicture.ZOrder msoSendToBack

'Get the Name of the first Cover Picture selected.
MsgBox ("shCoverPicture.Name = " & shCoverPicture.Name)
    
'Get the address of the selected Cell
strTheCellSelected = shCoverPicture.TopLeftCell.Address
MsgBox ("strTheCellSelected = " & strTheCellSelected)
    
'***** Stuck Here *****
'Get the .Name of the Picture/Shape that is undernearth the shCoverPicture that has been selected by the User.
zpos = -1    'impossible z position
For Each shp In ActiveSheet.Shapes
  If shp.TopLeftCell.Address = strTheCellSelected Then
    If shp.ZOrderPosition >= zpos Then
      Set TopMostShp = shp
      zpos = shp.ZOrderPosition
    End If
  End If
Next shp
MsgBox "Top shape name is " & TopMostShp.Name
End Sub
 
Hey p45cal - That worked like a charm.

Thanks so much for the break-through. I had been picking away at that for quite some time.

This solution taught me some new tricks... :clap2:
 
You must log in or register to reply here.
Back
Top