Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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.... "SDH" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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 "Corey" wrote: 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.... "SDH" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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) "SDH" wrote in message ... 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 "Corey" wrote: 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.... "SDH" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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 *. "Bob Phillips" wrote: 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) "SDH" wrote in message ... 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 "Corey" wrote: 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.... "SDH" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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(UniqueItem List) End If End If On Error GoTo 0 End Function "Bob Phillips" wrote: 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) "SDH" wrote in message ... 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 "Corey" wrote: 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.... "SDH" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button calculation from Userform and Cell reference
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) "SDH" wrote in message ... 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 *. "Bob Phillips" wrote: 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) "SDH" wrote in message ... 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 "Corey" wrote: 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.... "SDH" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform command button checkbox problem | Excel Programming | |||
need to minimize userform via command button on itself | Excel Programming | |||
Command button to run calculation function | Excel Programming | |||
Specifying a Command Button on a worksheet as differentiated from one on a UserForm | Excel Programming | |||
Userform disappears when you try to initialize from a command button | Excel Programming |