Image from Userform to Sheet

[Jimmypop]

Good day all

I have the code below which works and does pass the Image from Image1 control on my userform AddEmp to a sheet called "Pics" but not to the correct position...

The image is loaded and renamed to Image 1 on userform from Textbox 3 and 5. Now when I click to add to database it needs to do the follwong:

1. Take the image and place it in Cell C1 and then In A1 it needs the text in Textbox3 and in A2 it needs text from Textbox5 (Text boxes located on userform AddEmp).
2. Any subsequent entries need to go to next empty row....
3. Image needs to resize to fit inside the cell it is placed in.

Sub InsertImage(ImageFileName As String, ID As String, Empl As String)
    Dim Image As Object, t As Double, l As Double, w As Double, h As Double, ws As Worksheet, TargetCell As Range
    Set ws = Sheets("Pics")
    If ws Is Nothing Then Exit Sub ' Check if worksheet exists
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Get last used row in column A
    Set TargetCell = ws.Cells(lastRow + 1, 1) ' Set target cell to the next empty row on Pics sheet
    TargetCell = "'" & ID
    TargetCell.Offset(, 1) = Empl
    ' import picture
    Set Image = ws.Pictures.Insert(ImageFileName)
    ' cell position
    With TargetCell
        t = .Top
        l = .Left
        w = .Width
        h = .Height
    End With
    ' image placement
    With Image
        .Top = t
        .Left = l
        .Width = w
        .Height = h
        .Placement = xlMoveAndSize ' resize image to fit within the target cell
        .Name = ID
    End With
    Set Image = Nothing
End Sub

 

[NdNoviceHlp]

Hi Jimmypop. Maybe...

With TargetCell.Range

HTH. Dave

 

[Jimmypop]

Hi there

I then get an Argument not Optional error on line

With TargetCell.Range

 

[NdNoviceHlp]

Trial #2...

With Image.ShapeRange

Dave

 

[Jimmypop]

It does now insert the image, however...

1. A1 it needs the text in Textbox3 and in A2 it needs text from Textbox5 These items instead of A1 and A2 gets placed in B1 and B2. All text after this does go into next empty row... so this looks sorted
2. However all images gets placed in top left corner of cell G1 each time and not C1, C2,C3 etc...

 

[NdNoviceHlp]

Not sure why image would go to G1? Target cell is set for "A" & lastrow +1.

With TargetCell.Offset(0,3)

seems like it would place the image in "C"
Dave

 

[Jimmypop]

That is what is boggling my mind... With code above it now moves the image further to between column M and N so I checked and made pics the size of 200px by 200px and changed

With TargetCell.Offset(0,3)

to

With TargetCell.Offset(0,2)

Which did the trick.

But sometimes images are larger than 200 by 200.

can there be a way to make it that size before it is loaded to Image control on usefform or before it goes to the Pics Sheet?

 

[Jimmypop]

UPDATE:

Got the sizing to work... however some pics are still not placed in rows in column c... some of them are placed perfectly others are still way off...

 

[NdNoviceHlp]

I guess offset 3 from "A" would be "D" ... still drinking coffee. Maybe...

With TargetCell.Offset(0,2).Address

Dave

 

[Jimmypop]

Found the issue... it is when images are loaded to the userform image control.. they are loaded but rotate by itself it is these ones causing issue..will explain some more when at home