4

I'm fairly new to VBA, have been trying to work this one out but not having any luck.

I'm trying to create a product catalogue and have built a userform to allow new products to be entered on the first blank row of the spreadsheet. So far so good.

Where I'm getting stuck however, is that I need to embed a product image from a url in the userform (e.g. https://image.shutterstock.com/z/stock-photo-coffee-cup-and-coffee-beans-on-table-261745823.jpg) so that the file is able to be ported from computer to computer without losing the images already loaded onto the spreadsheet.

Ideally I don't want to have to insert the URL itself onto the spreadsheet at all. When the user hits Submit, I want the userform to pull the image from online & insert it into column A on the first blank row of the spreadsheet & resize it to a particular width/height. (to be determined).

I've found questions kind of similar to what I need here & here & here. The questions either aren't answered, or I'm not sure how to make them fit my needs.

This is the code I have now, however it presents a runtime 424 object error, I've clearly 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 :)

braX
  • 11,506
  • 5
  • 20
  • 33
Aktike
  • 41
  • 1
  • 3

1 Answers1

0

I am not really sure why do you have the runtime 424 object error. Can you specify which line of code gives you the error?

I would say this link Inserting an Online Picture to Excel with VBA has everything you really need.

You should declare an object variable for the picture

Dim myPicture As Picture 'embedded pic

And the repeat this every time you insert the picture:

You need to insert the picture to the worksheet by setting the value of myPicture object variable. In your case it would be something like this:

Set myPicture = Pictures.Insert(txtImgUrl.Value)

And then you add some details on how to position this picture on your worksheet

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
AdamW
  • 1
  • 1
  • Hi @AdamW, thanks for replying! I tried to update the code as you had suggested, but when I run it breaks at Set myPicture = Pictures.Insert(txtImgUrl.Value) – Aktike Mar 14 '18 at 21:57
  • It just keeps giving me the runtime 424 object error. I've checked spelling and everything so that's fine. I have declared the Dim as suggested. I'm not really sure where I'm going wrong? – Aktike Mar 14 '18 at 22:05
  • Ok, so maybe there is something wrong with the URL. Have you checked what’s inside the txtImgUrl.Value ? Try to to step by with F5 and see what’s inside. – AdamW Mar 15 '18 at 07:17
  • When I step into it, the line "Set myPicture = Pictures.Insert(txtImgUrl.Value)", myPicture = nothing & txtImgUrl = the url i entered from shutterstock to test. – Aktike Mar 18 '18 at 21:33