Send Element to Back

[natanz]

I found a macro that helped me insert a picture (jpeg file) in an
excel spreadsheet. Now I am looking for a way to send that element in
back of other elements on the spreadsheet. I have tried to record a
macro to figure out the methods and procedures, but apparently the
"send to back" buttons and "bring to front" are invisible to the macro
recorder. I found that the insert/shape/line tools are also
invisible. Finding a way to draw a line would be equally effective,
because if it is drawn following the insert it will effectively be on
top, which will meet my needs.

i appreciate any help you can provide. Thanks.

Natan

 

[Rick Rothstein]

Give this a try...

WS = "Sheet1"
SH = "Picture 1"
Worksheets(WS).Shapes(SH).OLEFormat.Object.BringToFront

Change the sheet name and and shape name that are assigned to the WS and SH
variables to match your actual conditions.

 

[Rick Rothstein]

Just remembered... you wanted to send the picture to the back, not bring it
to the front... use the SendToBack method instead of the BringToFront method
that I showed in my previous message.

WS = "Sheet1"
SH = "Picture 1"
Worksheets(WS).Shapes(SH).OLEFormat.Object.SendToBack

 

[natanz]

Thank you so much for your reply. It is very helpful. I hope you can
help again by answering a follow-up question. How do i know the names
of the shapes in my spreadsheet? Is it the filename of the picture.
Is it assigned by excel when the shape is inserted or drawn. I have
two shapes i am dealing with, 1 picture inserted, and 1 line drawn.
Ultimately, I want to programmatically draw this line and insert this
picture as well as "arrange" it, any help or direction you can give me
on those tasks would be awesome also.

Thanks for your help.

Natan

 

[Rick Rothstein]

If you have a shape already on the worksheet, you can get its name by
selecting it and then go into the VB editor and execute this line in the
Immediate Window....

? Selection.Name

Repeat the process if you have other existing shapes that you want to find
the names of. To draw a line into a worksheet, you would use the worksheet's
Shape object's AddLine method. As an example, this code...

Dim LineName As String
With Sheet1
.Shapes.AddLine 100, 100, 500, 500
LineName = .Shapes(.Shapes.Count).Name
End With
MsgBox LineName

will add a line at the indicated coordinates (see the help files for more on
the parameters) on Sheet1 and then display the name of the line in a
MessageBox. Dim the LineName variable outside of any procedure (or Dim it in
a Module depending on your scope requirements for it) to make it visible to
more than one procedure. To add a picture, use the Shape object's AddPicture
method (again, see the help files for its parameters).

--
Rick (MVP - Excel)


Thank you so much for your reply. It is very helpful. I hope you can
help again by answering a follow-up question. How do i know the names
of the shapes in my spreadsheet? Is it the filename of the picture.
Is it assigned by excel when the shape is inserted or drawn. I have
two shapes i am dealing with, 1 picture inserted, and 1 line drawn.
Ultimately, I want to programmatically draw this line and insert this
picture as well as "arrange" it, any help or direction you can give me
on those tasks would be awesome also.

Thanks for your help.

Natan