Hide command button behind pic

[ABabeNChrist]

I have a command button that is within a cell that when selected it insert a picture, the problem I have is when picture is in selected cell (same as command button) I can still view the command button from behind. I set the picture to “view from front” and Command button to “view from rear” but it doesn’t solve anything. I also have the command button checked to not print. Any ideas………………..

[Leith Ross]

Hello ABabeNChrist,

Do you want to hide the button, make it invisible, or just keep the button behind the picture?

[ABabeNChrist]

Hello ABabeNChrist,

Do you want to hide the button, make it invisible, or just keep the button behind the picture?

Hi Lieth Ross
I want the command button to be visible to use, but yet not printable, and once picture is selected the command button goes behind picture, and not visible view. I don’t want command button to be removed, just in case wrong selection was made, that way picture could be deleted and then reuse command button once more to make correct selection. Here is the code I use to inset and size picture.
And yes “just keep it behind picture”
And by the way I’m using Excel 2007

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("B1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation <> "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A1").Select

[Leith Ross]

Hello ABabeNChrist,

Is the picture going into cell "B1"? Is the CommandButton located in cell B1 also?

[ABabeNChrist]

Hi Leith Ross

Is the picture going into cell "B1

Yes

Is the CommandButton located in cell B1

Yes

[ABabeNChrist]

Any suggestion how I may achieve this.....

[Leith Ross]

Hello ABabeNChrist,

Here is the amended version of the macro...

Sub ResizePicture()

  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range
    
    Set MyRange = Range("B1")
    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation = "False" Then Exit Sub
    
    Set Pic = MyRange.Parent.Pictures.Insert(PicLocation)
    
      With Pic.ShapeRange
        .Left = MyRange.Left
        .Top = MyRange.Top
        .LockAspectRatio = msoFalse
        .ZOrder = msoBringForward
        If .Width > .Height Then
           .Width = MyRange.Width
           If .Height > MyRange.Height Then .Height = MyRange.Height
        Else
            .Height = MyRange.Height
            If .Width > MyRange.Width Then .Width = MyRange.Width
        End If
      End With
    
    With Pic
        .Placement = xlMoveAndSize
        .PrintObject = False
    End With
    
    Range("A1").Select
    
End Sub

[ABabeNChrist]

Hi Leith Ross
i just tried the code and I got an error on this line

.ZOrder = msoBringForward

Compile error
Argument not optional

I am using Excel 2007 if that make any difference

[Leith Ross]

Hello ABabeNChrist,

Sorry about that. Just force of habit to add an equal sign. The line should read...

  .ZOrder  msoBringForward

[ABabeNChrist]

OK the picture was inserted in correct cell and the height was correct but the width was too narrow.
I have a JPG that show picture to the left using new code. even when I try to expand the picture it still does not hide command button (picture to the right).
and I also set the .PrintObject = False to True