formula behind field on user form

[Lisa]

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

 

[Bernie Deitrick]

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]

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]

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