Lookup tables converted to userform with VBA

[molesy]

Hi
I have created some tables and used the lookup function to extract information. You enter the condition rating (1,2,0r 3) into table 1 column 2, and the result will appear in table 1 column 3. I would like to create a userform with the use of labels and textboxes extract the info onto the form. I have attached the file and hope someone would kindly put me in the right direction.

thanks in anticipation.

[ragulduy]

Hi, Based on a slightly shorter version of your formula in C4:
=OFFSET(E$1,MATCH(A4,E$1:E$30,0)+MATCH(B4,E$4:E$6,0),1)
the code in the userform for the textbox1_change event should be:

Private Sub TextBox1_Change()
TextBox6.Value = Evaluate("=OFFSET(E$1,MATCH(""" & Label1 & """,E$1:E$30,0)+MATCH(" & TextBox1.Value & ",E$4:E$6,0),1)")
End Sub

and the same for textboxes 7-10 change events., dependant on label 1-4 and textbox 1-4.

[molesy]

Thanks, works a treat, could i use spin buttons limiting numbers to 1,2 3

[ragulduy]

Don't see why not, you would need to put something like:

Private Sub SpinButton1_SpinUp()
TextBox1.value = worksheetfunction.min(TextBox1.value + 1,3)
TextBox6.Value = Evaluate("=OFFSET(E$1,MATCH(""" & Label1 & """,E$1:E$30,0)+MATCH(" & TextBox1.Value & ",E$4:E$6,0),1)")
End Sub
Private Sub SpinButton1_SpinDown()
TextBox1.value = worksheetfunction.max(TextBox1.value - 1,1)
TextBox6.Value = Evaluate("=OFFSET(E$1,MATCH(""" & Label1 & """,E$1:E$30,0)+MATCH(" & TextBox1.Value & ",E$4:E$6,0),1)")
End Sub

[molesy]

Thanks, it works very well.