displaying selection data from a listbox

[Mats Samson]

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

 

[STEVE BELL]

Here's something I used with a ComboBox to change labels on the form

=============================================
Private Sub ComboBox1_Change()
Dim rw As Long
rw = ComboBox1.ListIndex + 2
Label7.Caption = Sheets("MasterInventory").Cells(rw, 1)
Label8.Caption = Sheets("MasterInventory").Cells(rw, 2)
Label9.Caption = Sheets("MasterInventory").Cells(rw, 3)
Label10.Caption = Sheets("MasterInventory").Cells(rw, 4)
Label11.Caption = Sheets("MasterInventory").Cells(rw, 5)
End Sub
=========================================

You should be able to modify this for a ListBox,
and be able to add code to transfer to a worksheet.

Let me know if you need further info...

 

[Mats Samson]

Thanks,
but the problem persists! When I run the code the current customer (last
selected from previous "session") from the sheet is displayed in the labels
and this customer is also the selected one in the Listbox.
If select another customer (A) in the Listbox, nothing happens in the form
but the sheet is changed. Selecting next B updates the sheet to B but now A
is displayed in the lables, selecting C, displays B in the form and so on.....
Accordingly the labels are always one selection behind through
"Listbox_CHANGE"
and I can't get them to update to the current record.
It looks as follows:
======================
Public Sub UserForm_Initialize()
ListBox1.ColumnCount = 2
ListBox1.RowSource = "CustomerDB"
ListBox1.ControlSource = "a7"
ListBox1.BoundColumn = 0
End Sub
----------------------------------------
Private Sub ListBox1_Change()
Dim rw As Long
rw = ListBox1.ListIndex + 2
Label12.Caption = Sheets("DocSys").Range("Company")
Label13.Caption = Sheets("DocSys").Range("Address1")
Label14.Caption = Sheets("DocSys").Range("Address2")
Label15.Caption = Sheets("DocSys").Range("Zip")
Label16.Caption = Sheets("DocSys").Range("Town")
Label17.Caption = Sheets("DocSys").Range("Country")
Label18.Caption = Sheets("DocSys").Range("VAT")
End Sub
=========================
How can I get them to update to the same record?

 

[Mats Samson]

Thanks again,
I solved with the below code by simply change the event type from
Private Sub ListBox1_Change()
to
Private Sub ListBox1_AfterUpdate()
I understand it like it updates the change of record from Listbox to the
sheet.
THEN it updates the labels and voilá!
Regards
Mats

 

[STEVE BELL]

Mats,

Glad you were able to get it to work!

The difference between the 2 events slipped my mind.

keep on Exceling...