Send picture to the back

[Darkai]

Hi All
I have a problem. I'm trying to move the picture to the back as is pasted into the sheet because I have some text that needs to be in the front of the photo. Is this something you could help me with?
Thank you

Sub Insert_Photo()
Dim strFile As String
Dim rng As Range
Dim sh
Const cFile As String = "Image Files(*.bmp; *.jpg; *.jepg; *.png; *.tif),"

strFile = Application.GetOpenFilename(filefilter:=cFile, Title:=Ts)

If strFile = "False" Then
Else

Set rng = ActiveSheet.Range("d40").MergeArea
With rng
Set sh = ActiveSheet.Shapes.AddPicture(Filename:=strFile, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
sh.LockAspectRatio = msoFalse


End With
Set sh = Nothing
Set rng = Nothing
End If
End Sub

 

[Misca]

Welcome to the board!

Is the text written in a cell or is it in a text box? The pics and all the other objects in a worksheet "float" on top of the worksheet. If you want to bring the text on top of the picture make sure it's in a text box.

Once you have your your text in a text box you can arrange the floating objects in the preferred order using their ZOrderPosition property.

 

[Darkai]

Hi Misca
Text is written in a text box. How I can add this to my code ?
Thank you

 

[Misca]

Try something like:

Sub Insert_Photo()
Dim strFile As String
Dim rng As Range
Dim sh As Shape
Dim Ts As String
Const cFile As String = "Image Files(*.bmp; *.jpg; *.jepg; *.png; *.tif),"

strFile = Application.GetOpenFilename(filefilter:=cFile, Title:=Ts)

    If strFile = "False" Then
    Else
    
        Set rng = ActiveSheet.Range("d40").MergeArea
        
        With rng
            Set sh = ActiveSheet.Shapes.AddPicture(Filename:=strFile, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
        
            With sh
                .LockAspectRatio = msoFalse
                .ZOrder msoSendToBack   'Sends the image to the back
            End With
        
        End With
        
        Set sh = Nothing
        Set rng = Nothing
    End If

End Sub

The code sends the new image all the way to the back. See other alternatives from the MSOffice Documentations.

 

[Darkai]

Perfect !!!!! Thank you !!!!!

 

[Darkai]

Hi All
I tried to protect the worksheet so some of the cells can't be edited. Unfortunately, when I protected the worksheet my VBA button stopped working (Run-Time error 1004, The specified value is out of range) Can I add or change something in the code so that will work? Thank you in advanced for the time.
Kind Regards

 

[Misca]

You normally protect a worksheet when you want to avoid wrong kind of changes made to the worksheet. This applies to the macros as well as the users.

To solve this problem you have to unprotect the worksheet before you can make any changes and protect it again when the changes have been done. You can do that the a code as well by adding the unprotect line at the beginning of your code and protect line at the very end. You can find the lines you need from here with the full explanation.