Excel VBA Coding (extracting information from a list box)

[itrabohills]

Hi,

Please see attached, I have created a listbox which extracts data from an access database. This works fine, however the list box contains information which is too long and the user is unable to view all the contents. So, I want the user to be able to click on an 'asset' and for that to bring up a new userform with all the details associated to that particular asset. I have created the form, but am not sure how to start coding, or where I need to start coding, i.e. in the existing listbox or in the new form?? Sorry, to be so vague, I'm very new to this.

Many thanks!

[Kenneth Hobs]

Doubleclick the Listbox control and set the event that you want to trigger it. Use the column property of the listbox to get the parts that you need.

If your 2nd userform is not showing, do a UserForm2.Load and then set each textbox. You can use the Visible property of the Userform to check if it is visible or not.e.g. [vba]UserForm2.Load
UserForm2.TextBox1.Value=UserForm1.ListBox1.Column(0)
UserForm2.Show[/vba]
I used 3 methods below.

e.g.
[vba]Private Sub ListBox1_Click()
MsgBox ListBox1.Column(0) & vbCrLf & ListBox1.Column(1) & vbCrLf & ListBox1.Column(2)
End Sub

Private Sub CommandButton1_Click()
MsgBox ListBox1.Column(0) & vbCrLf & ListBox1.Column(1) & vbCrLf & ListBox1.Column(2)
End Sub

Private Sub ListBox1_Change()
MsgBox ListBox1.Column(0) & vbCrLf & ListBox1.Column(1) & vbCrLf & ListBox1.Column(2)
End Sub

Private Sub UserForm_Initialize()
Dim a() As String
ReDim a(1 To 3, 1 To 3)
a(1, 1) = "Apple"
a(1, 2) = "Banana"
a(1, 3) = "Grape"
a(2, 1) = "Red"
a(2, 2) = "Blue"
a(2, 3) = "Yellow"
a(3, 1) = "A"
a(3, 2) = "B"
a(3, 3) = "C"

ListBox1.ColumnCount = 3
ListBox1.List = a()
ListBox1.ListIndex = 0
End Sub[/vba]