Userform Help

[hemant1]

Firstly I'd like to thank anyone in advance who tries to help me
Here is a pic of my form:

Okay so basically within the Product ID Combo box I'd like it to display the whole item and not just the Item code

With the Price label (lblPriceformula) I'd like it to do a Vlookup from the Product ID to find a Price, I've got a list of prices which are on the next column to the Product name and have defined these as Pricelist but after alot! of google searches I can't seem to find out how to use this.

Lastly for the TotalPrice label how would I got about multiplying the quantity with the Price? I had a thought that it could be =txtQty*lblPriceformula but I really doubt its gonna work :l

At the moment I'm using 3 named ranges (LookupLists is the sheets name)
LocationList (Which refers to the supplier combobox)

=OFFSET(LookupLists!$E$2,0,0,COUNTA(LookupLists!$E:$E)-1,1)

PartIDList (Which refers to the Product ID)

=OFFSET(LookupLists!$A$2,0,0,COUNTA(LookupLists!$A:$A)-1,1)

PartsLookup (Which is the item name)

=OFFSET(PartIDList,0,0,,2)

The Price lookup I have made is : (not sure if this one is right though)

PriceList

=OFFSET(LookupLists!$C$2,0,0,COUNTA(LookupLists!$C:$C)-1,1)

The code I'm using at the moment is from a tutorial and is :

Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cPart In ws.Range("PartIDList")
  With Me.cboPart
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus

End Sub

I am pretty much a noob with code and thank you again for helping Oh and if you need any more info please dont hesitate to ask

 

[Norie]

Use the combobox's Change event, which you can get by simply double clicking the combobox while in design view.

You'll then see something like this in the code window.

Private Sub cboPart_Change()
    ' check a part has been selected
    If cboPart.ListIndex <> -1 Then
         lblPriceformula.Caption = Range("Pricelist").Cells(cboPart.ListIndex + 1).Text
         lblTotalPrice.Caption =Val(txtQty.Value)*Val(lblPriceformula.Caption)
    End If
End Sub

PS Please remember what I said about currency format.

 

[hemant1]

On your userform you have a drop down which lists your products, this a combo box, in design view of the form, you double click the combo to go into the code behind it, then at the top right it lists your procedures, so you click the list and look for After Update. Then add the code in there.

Thank you so much I really appreciate it! it's worked

Use the combobox's Change event, which you can get by simply double clicking the combobox while in design view.

You'll then see something like this in the code window.

Private Sub cboPart_Change()
    ' check a part has been selected
    If cboPart.ListIndex <> -1 Then
         lblPriceformula.Caption = Range("Pricelist").Cells(cboPart.ListIndex + 1).Text
         lblTotalPrice.Caption =Val(txtQty.Value)*Val(lblPriceformula.Caption)
    End If
End Sub

PS Please remember what I said about currency format.

It's worked! thank you so much for helping

 

[hemant1]

One sec guys I have one more thing I kinda need help with lemme quickly post a pic ill edit this in like 2 mins

In the dropdown how do I get it to show the full name because at the moment it's cutting most of it out!

Here is the code for it (I think this is the code which refers to it) :

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc

 

[hemant1]

Quick bump I've almost finished my whole system!

 

[Norie]

Change the ColumnWidth.

 

[hemant1]

Change the ColumnWidth.

It worked thank you!