formula behind field on user form
 

Hi

I have drop down lists within a worksheet, which allow me to key in a
select the supplier, itema, itemb, and retrieve the price. I have gotten this
working perfectly by using named ranges and the following formula

=SUMPRODUCT(--(supplier=G2),--(itema=H2),--(itemb=I2), (Price))

I now want to move the functionality to a user form. I have managed to put
my named ranges into the RowSource properties of each of my list boxes on the
form, however, I need to place this formula somewhere against my 'price'
field in order that it retrieves the price based on the supplier, item etc
selecte.

Thanks in advance

formula behind field on user form
 

Lisa,

Assuming all the values are on the Userform:


Private Sub CommandButton1_Click()
Dim myform As String
myform = "SumProduct((Supplier = """ & UserForm1.TextBox1.Text & """)*(ItemA = """ &
UserForm1.TextBox2.Text & """)*(ItemB= """ & UserForm1.TextBox3.Text & """)*Price)"
Msgbox "The price for that item is " & Evaluate(myform)
End Sub

If any of the Textbox entries are numbers, then you will need to get rid of the extra "" and cast
the text into a number using CInt or CDbl....


HTH,
Bernie
MS Excel MVP


"Lisa" wrote in message
...
Hi

I have drop down lists within a worksheet, which allow me to key in a
select the supplier, itema, itemb, and retrieve the price. I have gotten this
working perfectly by using named ranges and the following formula

=SUMPRODUCT(--(supplier=G2),--(itema=H2),--(itemb=I2), (Price))

I now want to move the functionality to a user form. I have managed to put
my named ranges into the RowSource properties of each of my list boxes on the
form, however, I need to place this formula somewhere against my 'price'
field in order that it retrieves the price based on the supplier, item etc
selecte.

Thanks in advance

formula behind field on user form
 

Great, thanks Bernie. I'd like to have a field at the bottom of the form that
displays the price, rather than a button, would the macro be the same?

Will definitely give your suggestion a go anyway.

Thanks again

"Bernie Deitrick" wrote:

Lisa,

Assuming all the values are on the Userform:


Private Sub CommandButton1_Click()
Dim myform As String
myform = "SumProduct((Supplier = """ & UserForm1.TextBox1.Text & """)*(ItemA = """ &
UserForm1.TextBox2.Text & """)*(ItemB= """ & UserForm1.TextBox3.Text & """)*Price)"
Msgbox "The price for that item is " & Evaluate(myform)
End Sub

If any of the Textbox entries are numbers, then you will need to get rid of the extra "" and cast
the text into a number using CInt or CDbl....


HTH,
Bernie
MS Excel MVP


"Lisa" wrote in message
...
Hi

I have drop down lists within a worksheet, which allow me to key in a
select the supplier, itema, itemb, and retrieve the price. I have gotten this
working perfectly by using named ranges and the following formula

=SUMPRODUCT(--(supplier=G2),--(itema=H2),--(itemb=I2), (Price))

I now want to move the functionality to a user form. I have managed to put
my named ranges into the RowSource properties of each of my list boxes on the
form, however, I need to place this formula somewhere against my 'price'
field in order that it retrieves the price based on the supplier, item etc
selecte.

Thanks in advance

formula behind field on user form
 

Lisa,

The macro to evaluate the sumproduct expression would be essentially the same, but you would need to
tie it to a change event, and do some error checking for blank fields, perhaps.

HTH,
Bernie
MS Excel MVP


"Lisa" wrote in message
...
Great, thanks Bernie. I'd like to have a field at the bottom of the form that
displays the price, rather than a button, would the macro be the same?

Will definitely give your suggestion a go anyway.

Thanks again

"Bernie Deitrick" wrote:

Lisa,

Assuming all the values are on the Userform:


Private Sub CommandButton1_Click()
Dim myform As String
myform = "SumProduct((Supplier = """ & UserForm1.TextBox1.Text & """)*(ItemA = """ &
UserForm1.TextBox2.Text & """)*(ItemB= """ & UserForm1.TextBox3.Text & """)*Price)"
Msgbox "The price for that item is " & Evaluate(myform)
End Sub

If any of the Textbox entries are numbers, then you will need to get rid of the extra "" and cast
the text into a number using CInt or CDbl....


HTH,
Bernie
MS Excel MVP


"Lisa" wrote in message
...
Hi

I have drop down lists within a worksheet, which allow me to key in a
select the supplier, itema, itemb, and retrieve the price. I have gotten this
working perfectly by using named ranges and the following formula

=SUMPRODUCT(--(supplier=G2),--(itema=H2),--(itemb=I2), (Price))

I now want to move the functionality to a user form. I have managed to put
my named ranges into the RowSource properties of each of my list boxes on the
form, however, I need to place this formula somewhere against my 'price'
field in order that it retrieves the price based on the supplier, item etc
selecte.

Thanks in advance