lisbox reference and calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a lisbox(lboProduct) on a userform (UserForm3). The listbox references
a list of products on a worksheet (Sheet1) in column A. In column B of Sheet1
is the corresponding price of each product. The UserForm3 also has a TextBox
(txtQuantity). I have a command button (cmdCost) that when clicked needs to
show the total cost. Which is equal to the amount entered in txtQuantity *
the corresponding price of the product selected in the lboProducts.
 
I have a lisbox(lboProduct) on a userform (UserForm3). The listbox references
a list of products on a worksheet (Sheet1) in column A. In column B of Sheet1
is the corresponding price of each product. The UserForm3 also has a TextBox
(txtQuantity). I have a command button (cmdCost) that when clicked needs to
show the total cost. Which is equal to the amount entered in txtQuantity *
the corresponding price of the product selected in the lboProducts.
Click to expand...

And your question is .......
 
Hello
Create two names on sheet1:
"Product" to refer to product names in column A
"Price" to refer to prices in column B
Then in the CmdCost_CLick place this:

Private Sub CmdCost_Click()
If Me.txtQuantity.Value <> "" Then
prod = Me.lboProduct.Value
Pprice = Application.WorksheetFunction.Index(Range("Price") _
, Application.WorksheetFunction.Match(prod, Range("Product"), 0))
MsgBox Pprice * txtQuantity
End If
End Sub

HTH
Cordially
Pascal
 
thanks very much papou that works fine

papou said:
Hello
Create two names on sheet1:
"Product" to refer to product names in column A
"Price" to refer to prices in column B
Then in the CmdCost_CLick place this:

Private Sub CmdCost_Click()
If Me.txtQuantity.Value <> "" Then
prod = Me.lboProduct.Value
Pprice = Application.WorksheetFunction.Index(Range("Price") _
, Application.WorksheetFunction.Match(prod, Range("Product"), 0))
MsgBox Pprice * txtQuantity
End If
End Sub

HTH
Cordially
Pascal
Click to expand...
 
Back
Top