Insert an image from a URL in an Excel custom form

Question

I am new to VBA, tried to work on this, but had no luck.

I am trying to create a product catalog and created a custom form that allows me to enter new products in the first empty row of the table. So far, so good.

However, when I'm stuck, I need to embed the product image from the URL in a custom form (e.g. https://image.shutterstock.com/z/stock-photo-coffee-cup-and-coffee-beans-on-table -261745823.jpg ), so that the file can be ported from computer to computer without losing images already downloaded to the spreadsheet.

Ideally, I don't want to embed the URL into the spreadsheet at all. When the user clicks Submit, I want the user form to display the image from the network and insert it into column A in the first empty row of the table and resize it to a certain width / height. (to be determined).

I found questions like what I need here and here and here . The questions are either not answered, or I'm not sure how to make them suitable for my needs.

This is the code that I have now, however it represents an object 424 error at runtime, I explicitly missed something> <

Private Sub cmdSubmitForm_Click()

Dim StorePrice As Currency, SupplierPrice As Currency
Dim pic As String 'file path of pic

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Pricing")

'Finds the last blank row
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1

StorePrice = txtStorePrice.value
SupplierPrice= txtSupplier.Price.Value

ws.Range("B" & LastRow).Value = txtProductName.Text
ws.Range("C" & LastRow).Value = txtStorePrice.Text
ws.Range("D" & LastRow).Value = cboCategory.Text
ws.Range("E" & LastRow).Value = cboSubCategory.Text
ws.Range("F" & LastRow).Value = txtStoreLink.Text
ws.Range("G" & LastRow).Value = txtSupplierName.text
ws.Range("H" & LastRow).Value = txtSupplierPrice.text
ws.Range("I" & LastRow).Value = txtSupplierLink.text
ws.Range("J" & LastRow).Value = StorePrice- SupplierPrice
ws.Range("K" & LastRow).Value = txtNotes.Text

ws.Range("A" & LastRow).Value = Pictures.Insert(txtImgUrl.Value)


'handler:
'MsgBox "Error"

End Sub

If anyone has any ideas on how to solve this, I would really appreciate it !!!!

Thanks in advance:)

Answer 1

, 424 . , ?

, - Excel VBA , .

Dim myPicture As Picture 'embedded pic

, :

, myPicture. :

Set myPicture = Pictures.Insert(txtImgUrl.Value)

, .

With myPicture
    .ShapeRange.LockAspectRatio = msoFalse 'Unlock aspect ratio
    .Width = ws.Range("A" & LastRow).Width 'Picture width equal to cell width
    .Height = ws.Range("A" & LastRow).Height 'Picture height equal to cell height
    .Top = Rows(ws.Range("A" & LastRow).Row).Top 'Picture aligned to the top edge of the cell
    .Left = Columns(ws.Range("A" & LastRow).Column).Left 'Picture aligned to the left edge of the cell
End With