Inserting Formala in a Userform TextBox

Posted by Adrian Williams on March 31, 2001 6:39 PM

I am trying to have a textbox return the contents of a cell and I need to use a formula to return the value. I am using

Private Sub ComboBox1_Change()
UserForm1.TextBox1.SetFocus
UserForm1.TextBox1.Value = VLookup(Lookup, Table, 3, False)
End Sub

An the VBE won't allow this, can anybody help?

Posted by Dave Hawley on March 31, 2001 6:43 PM

Hi Adrian

Change it to:

UserForm1.TextBox1.Value = WorksheetFunction.VLookup _
(Lookup, Range("Table"), 3, False)

Dave

OzGrid Business Applications

Posted by Adrian Williams on April 01, 2001 1:48 AM

Dave,

Thanks Dave, but I can't seem to make it work. I've cut and pasted it within the Private Sub ComboBox1_Change()and no luck.

Am I doing something wrong?

Regards

Posted by Dave Hawley on April 01, 2001 3:11 AM

Adrian, I'm assuming "lookup" is a string variable and "Table" is a named range ?

You should really use

ComboBox1_Change()
If ComboBox1.ListIndex>-1 then
Lookup=Combobox1
UserForm1.TextBox1.Value = WorksheetFunction.VLookup _
(Lookup, Range("Table"), 3, False)
End if
End sub

Dave

OzGrid Business Applications

Posted by Adrian Williams on April 01, 2001 3:34 AM

Cheers Dave- Works Great