Need assistance VBA LoadPicture in form with variable

[HDfatboy03]

Hello

I am need of help with VBA LoadPicture in a form based on a variable.

My combo box is the variable = cboitem
my image box is = Image1
my form is named = frmData
my pics are located = C:\Bob\Inventory_Tracker\

When the user ... lets say selects item # 13200 in the combo box ... I would like the
corresponding picture 13200.jpg to show up in the form (Image1). My form works but I can't
figure out the picture part. Here is what I have so far
----------------------------------------------
Private Sub cboItem_Change()
Dim r As Long
r = Me.cboItem.ListIndex + 2
Me.txtLoc.Value = Worksheets("DB").Cells(r, 11).Value
Me.txtDesc.Value = Worksheets("Db").Cells(r, 3).Value
Me.txtQty.Value = Worksheets("Db").Cells(r, 4).Value
Me.txtCost.Value = Worksheets("Db").Cells(r, 10).Value

End Sub
Private Sub cmdADD6_Click()
Dim iRow As Long
w = CLng(Me.txtLoc.Text)
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws2 = Worksheets("Db")
Set ws = Worksheets("Cart")
Set ws2 = Worksheets("Db")
'calculation for inventory sold
Dim Z As Integer
x = CLng(Me.txtQty.Text)
y = CLng(Me.txtBuy.Text)
Z = x - y

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to Cart
ws.Cells(iRow, 1).Value = Me.cboItem.Value
ws.Cells(iRow, 2).Value = Me.txtDesc.Value
ws.Cells(iRow, 3).Value = Me.txtCost.Value
ws.Cells(iRow, 4).Value = Me.txtBuy.Value
ws.Cells(iRow, 5).Value = Me.txtPrice.Value

'copy adjusted inventory level
ws2.Cells(w, "D").Value = Z

'clear the data
Me.cboItem.Value = ""
Me.txtDesc.Value = ""
Me.txtCost.Value = ""
Me.txtBuy.Value = ""
Me.txtPrice.Value = ""

End Sub
Private Sub cmdCLOSE_Click()
Unload Me
End Sub
Private Sub Image1_Click()
End Sub
Private Sub Label9_Click()
End Sub
Private Sub txtDate_Change()
End Sub
Private Sub UserForm_Initialize()
'adds date automatically
Me.txtDate.Value = Format(Date, "Medium Date")
End Sub
------------------------------------------------
In my research I have found this code ... but I am unable to fit into my existing code ...
please help
------------------------------------------------
Private Sub UserForm_Initialize()
PicFile = Dir("C:\Bob\Inventory_Tracker\*.jpg")
Do While PicFile <> ""
PicFile = Dir
cboitem.AddItem PicFile, cboitem.ListCount
Loop
End Sub
Private Sub cboitem_Change()
On Error GoTo NoPic
Image1.Picture = LoadPicture("C:\Bob\Inventory_Tracker\" & cboitem.Value)
Exit Sub
NoPic:
Image1.Picture = LoadPicture()
End Sub
------------------------------------------------
Any help will be greatly appreciated ... thanks in advance ... Bob

 

[AlphaFrog]

Private Sub cboItem_Change()
    Dim r      As Long
    [COLOR="Red"]Dim strPic As String[/COLOR]

    r = Me.cboitem.ListIndex + 2
    Me.txtLoc.Value = Worksheets("DB").Cells(r, 11).Value
    Me.txtDesc.Value = Worksheets("Db").Cells(r, 3).Value
    Me.txtQty.Value = Worksheets("Db").Cells(r, 4).Value
    Me.txtCost.Value = Worksheets("Db").Cells(r, 10).Value

[COLOR="Red"]    If cboitem.ListIndex > -1 Then
        srPic = "C:\Bob\Inventory_Tracker\" & cboitem.Value & ".jpg"
        If Len(Dir(srPic)) Then
            Image1.Picture = LoadPicture(srPic)
        Else
            Image1.Picture = LoadPicture("")
        End If
    Else
        Image1.Picture = LoadPicture("")
    End If[/COLOR]

End Sub

 

[HDfatboy03]

Alpha Frog

Thanks for the help ... but the pic is still not showing up ... what could it be?

 

[AlphaFrog]

My 1st guess is that the value in the combobox is not the exact file name

If the value in cboItem is exactly 13200, then this line...
srPic = "C:\Bob\Inventory_Tracker\" & cboitem.Value & ".jpg"

...should make the file path and name
C:\Bob\Inventory_Tracker\13200.jpg

Does that file exist?


Here's a good primer on Debugging VBA code.
Debugging VBA

 

[HDfatboy03]

Thank you sir ... it works now ... I do have one more question. My inventory pics are 1000 pixels by 1000 pixels and the program crashes ... but when I modify them manually to 200 x 200 it works.

Do you know how I can resize them in VBA to 200 x 200?

thanks again

Bob

 

[AlphaFrog]

I don't know how to resize the number of pixels of an image file in VBA. There are several other applications (e.g. Picasa) that could batch resize your images.

Picture size shouldn't matter though. I can load much larger pictures into an Image object with no problem.

 

[HDfatboy03]

Thankyou sir ... you have been very kind to me ... and I will take a look into Picasa ... thanks again