Prefill txt box from drop down list code

ipbr21054 · Sep 11, 2018

Evening all,

I am making a small userform & would like to ask for some help / assistance with its set up if i may.
Currently i am able to select an item from the drop down list & its image is shown but would like the following to be also included.

Database is as follows.
Column A = Item Number
Column E = Cost
Column F = Url to image

So when i make a selection from the drop down list i would like to also see the txt boxes mentioned in the code below filed with the info that will be in the cell from column A & E mentioned above.

You will also see there is a CommandButton2 of which when pressed would take me to the image on the Internet using the info from whats in the cell in column F

I have currently the code as shown below.

Code:

Option ExplicitPrivate Sub cmbImg_Change()
On Error Resume Next
Dim myImg
myImg = Me.cmbImg.List(Me.cmbImg.ListIndex, 2)
Me.Image1.Picture = LoadPicture(myImg)


Me.Label1 = Me.cmbImg.List(Me.cmbImg.ListIndex, 1)
End Sub


Private Sub CommandButton2_Click()


End Sub


Private Sub txtCost_Change()


End Sub


Private Sub txtItemNumber_Change()


End Sub

ipbr21054 · Sep 12, 2018

Any guidance please for this.

Thanks

ipbr21054 · Sep 12, 2018

I have been reading about INDEX MATCH but can i use it in VBA so i can complete my request as in post 1 ?

ipbr21054 · Sep 12, 2018

Time was running away so had to ditch the above code and start again.
The code in use is now shown below but stuck with importing the picture.

The part number which is collected from column A is also the name of the photo which its path is as follows,
C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\

Below i know i have it wrong but could you advise please,
Me.Image1 = LoadPicture(C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME".jpg")

Code:

Private Sub ComboBox1_DropButt*******()Dim i As Long, LastRow As Long
LastRow = Sheets("LPM").Range("A" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LastRow
Me.ComboBox1.AddItem Sheets("LPM").Cells(i, "A").Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("LPM").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("LPM").Cells(i, "A").Value = (Me.ComboBox1) Or _
Sheets("LPM").Cells(i, "A").Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets("LPM").Cells(i, "B").Value
Me.TextBox2 = Sheets("LPM").Cells(i, "E").Value
Me.TextBox3 = Sheets("LPM").Cells(i, "F").Value
Me.TextBox4 = Sheets("LPM").Cells(i, "C").Value
Me.Image1 = LoadPicture(C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\".jpg")
End If
Next
End Sub

Many Thanks

ipbr21054 · Sep 12, 2018

Ive tried the following but no joy.

Me.Image1 = LoadPicture(C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME".jpg")
Me.Image1.Picture = LoadPicture("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\.jpg")

What did i do wrong as i cant see it ??

ipbr21054 · Sep 13, 2018

Hi,
Is there anybody available to assist with the correct path code please.

This is in place at present,
Me.Image1.Picture = LoadPicture("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\015006-1.jpg")

So if i select from the drop down box 015006-1 then the image is shown perfect.

The code needs to lets say A Wild Card so when i select any number from the drop down list it imports the same photo.
So select say 1234 it then imports 1234
select 992711 would then import 992711

I cant work out the code to do this.
The path in my code at present is correct as it imports the image but need to add the wild card so it isnt a fixed image in the code but the wild card etc.

I think you know what mean as im not to clever explaining these issues.

Thanks

Scott Huish · Sep 13, 2018

I played with this a little bit and I think you need to redraw the form after you load the picture.

Try this:

Code:

Me.Image1.Picture = LoadPicture("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\015006-1.jpg")
Me.Repaint

ipbr21054 · Sep 13, 2018

Thanks but by putting 015006-1 in will only load that photo.
I am sure that something like "/" etc etc acts as a wild card so it then imports the same as the combobox selection.

Scott Huish · Sep 13, 2018

Just pull that value into your string:

Code:

Me.Image1.Picture = LoadPicture("C:\Users\Ian\Desktop\SKYPE\LOCK PICK ME\" & Me.ComboBox1.Value & ".jpg")
Me.Repaint

strooman · Sep 13, 2018

Assuming this is your setup:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][/tr][tr][td]

1

[/td][td="bgcolor:#00B0F0"]Item_Number[/td][td="bgcolor:#00B0F0"][/td][td="bgcolor:#00B0F0"][/td][td="bgcolor:#00B0F0"][/td][td="bgcolor:#00B0F0"]Cost[/td][td="bgcolor:#00B0F0"]Name[/td][/tr]
[tr][td]

2

[/td][td]

10254