Hello,
I'm currently building a "simple" purchase, order and invoice registrator.
(I could be using Access but I'm much better with Excel and surprisingly,
it's much easier to create documents for printing in Excel than is Access)
I'm trying to display database records from a selected item in a listbox.
In fact it should return a selected customer and his addressinformation, but
displayed on the form itself. At the same time, the records should appear in
the sheet that will be printed. This last piece I've completed but I have a
problem to display the selected customer and his data on the form (as a
verification).
The vba-form has a listbox where I get the database 2nd column (Customercode)
(the first column is an index-no.). Either labels, textboxes or another
listbox
should return the customer name and address information like a standard
letterhead. I'ver tried several methods, using
'worksheetfunction.vlookup....',
'Labelx.Caption=Company' (Company is a variable) and 'Textbox.text= Company
but it fails all the time. Either if I put it in the Userform_Initialize it
displays once an never changes again (if I select customers twice in the
listbox), or it displays the previous record when I select another customer
from the list. If I put the code in the
Listbox1_Change procedure, I get declaration errors or as in the case with
Vlookup
"Unable to get the Vlookup property of the Worksheet function class". Very
enlightening! Why the h..k does it fail when putting the worksheet function
in the _change procedure?
I sure I'm doing some VBA-logical mistake but now now I'm so frustrated and
tested so many variants that I can't think straight!
It looks something like following:
Public Sub UserForm_Initialize()
ListBox1.ColumnCount = 8
ListBox1.RowSource = "CustomerDB"
ListBox1.ControlSource = "a7" 'sheet control for Vlookup for letterhead in
doc.
ListBox1.BoundColumn = 0

Sub ListBox1_Change()
TextBox1.Text = _
WorksheetFunction.VLookup(ListBox1.Value, Range("Database"), 2, False)
TextBox2.Text = _
WorksheetFunction.VLookup(ListBox1.Value, Range("Database"), 3, False)

or in another test I used

Label12.Caption = Company
Label13.Caption = Address1
Label14.Caption = Address2

or

TextBox6.Text = Company
TextBox7.Text = Address1
TextBox8.Text = Address2

The only way of getting the record change with every new selection of the
customer was with

TextBox1.Text = ListBox1.Text

but, then I couldn't display the other records in the database, only the
record from the BoundColumn.

Is there a simple solution? Surely, but I can't see it right now!
Best regards
Mats Samsson