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

 

[guerillaunit]

Hey hemant, it looks like your pic didn't quite come through. Can you try uploading it again?

 

[hemant1]

Hey hemant, it looks like your pic didn't quite come through. Can you try uploading it again?

Reuploaded hopefully it'll work:

 

[hemant1]

Bump! (Sorry if I'm not allowed to bump so soon )

 

[Trevor G]

I think you would need to upload a sample workbook some where so that someone can take a look. If I understand this you want data to appear on a form, once you select from a combo and then do some stuff and work out a total price is that correct?

If you can get it uploaded (perhaps use Google or Microsoft) then I will take a look for you.

 

[Norie]

You don't need a Vlookup to get the price.

You should be able to use the named range of prices with the ListIndex from the parts combobox.

lblPriceformula.Caption = Range("Pricelist").Cells(cboPart.ListIndex + 1).Text

As for multiplying the price and quantity you can try this.

lblTotalPrice.Caption =Val(txtQty.Value)*Val(lblPriceformula.Caption)

Note this won't work if the price is formatted as currency.

 

[hemant1]

I think you would need to upload a sample workbook some where so that someone can take a look. If I understand this you want data to appear on a form, once you select from a combo and then do some stuff and work out a total price is that correct?

If you can get it uploaded (perhaps use Google or Microsoft) then I will take a look for you.

Yh I would've uploaded but I can't because of it being cw and stuff but thank you anyway

You don't need a Vlookup to get the price.

You should be able to use the named range of prices with the ListIndex from the parts combobox.

lblPriceformula.Caption = Range("Pricelist").Cells(cboPart.ListIndex + 1).Text

As for multiplying the price and quantity you can try this.

lblTotalPrice.Caption =Val(txtQty.Value)*Val(lblPriceformula.Caption)

Note this won't work if the price is formatted as currency.

It worked! but do you know how to make it happen automatically instead of having it done once you click the label? so I mean as soon as I choose the product it automatically updates the price?

 

[Trevor G]

Use the Combo Event After Update and add the code in there.

 

[hemant1]

Use the Combo Event After Update and add the code in there.

I'm really sorry for being so clueless but what's a Combo Event?

 

[Trevor G]

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.