Insert No linked picture in the cell with formatting VBA

7absinth · Dec 1st 2016

I found the difficulties to find the right approach in Excel HOW I can to format inserted object (picture) with line borders and colors etc.

Code

Option Explicit
Sub ImportPictureNoLinked()




Dim fd As Office.FileDialog, pic As Object




Set fd = Application.FileDialog(msoFileDialogFilePicker)




With fd
.AllowMultiSelect = False
.Title = "Please select the file"
.Filters.Add "Images", "*.gif; *.jpg; *.jpeg; *.bmp; *.tif; *.png"
.FilterIndex = 2




If .Show = -1 Then




Set pic = ActiveSheet.Shapes.AddPicture(Filename:=.SelectedItems(1), LinkToFile:=False, _
SaveWithDocument:=True, Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
Width:=ActiveCell.Width, Height:=ActiveCell.Height)




'<HERE I WANT A CODE THAT MAKE INSERTED PICTURE BORDER LINE WITH COLOR AND DISABLE LOCK ASPECT RATIO>




Set pic = Nothing




End If
End With
End Sub

Display More

**Kenneth Hobson** · Dec 2nd 2016

Re: Insert No linked picture in the cell with formatting VBA

I am not sure if you mean the shape or the cell in the topleft. This shows how to work with both objects.

Code

Sub ImportPictureNoLinked()
  Dim fd As Office.FileDialog, pic As Shape
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file"
    .Filters.Add "Images", "*.gif; *.jpg; *.jpeg; *.bmp; *.tif; *.png"
    .FilterIndex = 2
  
    If .Show = -1 Then
      Set pic = ActiveSheet.Shapes.AddPicture(Filename:=.SelectedItems(1), LinkToFile:=False, _
      SaveWithDocument:=True, Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
      Width:=ActiveCell.Width, Height:=ActiveCell.Height)
       '<HERE I WANT A CODE THAT MAKE INSERTED PICTURE BORDER LINE WITH COLOR AND DISABLE LOCK ASPECT RATIO>
        With pic
          With .TopLeftCell.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = 3
          End With
        End With
        Set pic = Nothing
    End If
    End With
End Sub

Display More

7absinth · Dec 2nd 2016

Re: Insert No linked picture in the cell with formatting VBA

Nice idea to define as shape but I mean this picture in the cell. Macro works but it doesnt stretch with the cell (LockAspectRatio) as it's when pic variable define as picture. And here I am stuck.

**Kenneth Hobson** · Dec 3rd 2016

Re: Insert No linked picture in the cell with formatting VBA

Select is the easiest way handle some of things that you want.

Code

With pic
              .LockAspectRatio = msoCTrue
              '.Height = ActiveCell.Height * 0.75
              .Select
              'Selection.ShapeRange.Reflection.Type = msoReflectionType1
              With Selection.ShapeRange.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
             End With
            .ScaleHeight 0.75, msoFalse

Display More

If you need to get a pic's dimensions to resize before your AddPicture, I can show you a method or two.

7absinth · Dec 4th 2016

Re: Insert No linked picture in the cell with formatting VBA

Seems I found the solution. Thank you for your help.:thumbcoo:

I wrote the following lines:

Code

With pic
            .LockAspectRatio = msoFalse
            .Placement = xlMoveAndSize
        End With

AND gotcha.

Insert No linked picture in the cell with formatting VBA

Participate now!

Share