Command button calculation from Userform and Cell reference

[Guest]

I have a userform that has a 'Product' listbox and a 'Quantity' textbox. The
cost of the 'Product' is on a product worksheet. I have a command button on
the Userform called 'Cost'. When clicked i need this button to calculate the
cost of the purchase i.e. purchase price = 'quantity' in text box multiplied
by cost of product. I would like it to be displayed either in a userform or a
message box with an ok button.

 

[Corey]

something like:

Private Sub CommandButton1_Click()
If ListBox1.Value <> "" And TextBox1.Value <> "" Then
MsgBox "Total Purchase price is " & ListBox1.Value * TextBox1.Value
End If
End Sub


Where Listbox1 = Product
and
Textbox1 = Quanity


Corey....
I have a userform that has a 'Product' listbox and a 'Quantity' textbox. The
cost of the 'Product' is on a product worksheet. I have a command button on
the Userform called 'Cost'. When clicked i need this button to calculate the
cost of the purchase i.e. purchase price = 'quantity' in text box multiplied
by cost of product. I would like it to be displayed either in a userform or a
message box with an ok button.

 

[Guest]

Very similar Corey, except that the Product ListBox1 is the name of the
product and refers to a cell in column A of the worksheet, where the price of
the product selected is in column B and is the required reference to multiply
against the Quantity entered in TextBox1

 

[Bob Phillips]

iPos = 0
On Error Resume Next
iPos = Application.Match(lstProducts.Value,
Worksheets("Data").Columns(1),0)
On Error Goto 0
If iPos > 0 Then
MsgBox "Total Purchase price is " &
Worksheets("Data").Cells(iPos,2).Value,* txtQty.Value
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

thanks Bob, but it is still not working. i have enough knowledge to be
dangerous as they say. i get a syntax error on the MsgBox line at the *.

 

[Guest]

this is the code i have for my lstProducts if that helps.

Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If

 

[Bob Phillips]

I left a stray comma in there, it should be

Worksheets("Data").Cells(iPos,2).Value* txtQty.Value

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)