Croeg
06-04-2007, 10:02 PM
Hello,
With help from this site, I've created a form with a listbox that when clicked will populate multiple textboxes on the form in relation to what is highlighted in the listbox. Code below:
Private Sub UserForm_Initialize()
ListBox1.RowSource = "=OFFSET(LIST!$A$2,0,0,COUNTA(LIST!$A:$A),COUNTA(LIST!$1:$2))"
End Sub
Private Sub ListBox1_Click()
Dim c
With ActiveSheet.Range("H:H")
Set c = .Find(ListBox1, LookIn:=xlValues)
wb.Visible = False
If Not c Is Nothing Then
TextBox1 = c.Offset(0, 0).Value
TextBox2 = c.Offset(0, -1).Value
TextBox3 = c.Offset(0, 1).Value
Label27 = c.Offset(0, -5).Value
TextBox7 = c.Offset(0, 5).Value
TextBox10 = c.Offset(0, 3).Value
TextBox14 = c.Offset(0, 4).Value
TextBox15 = c.Offset(0, -2).Value
End If
End With
End sub
The trouble I am running into is that the code seems to filter data. I would like to be able to scroll down the listbox and view the offset data exactly as it is on the spreadsheet (without filtering).
I hope I've provided enough data to help.
Below is the type of data in each column of Sheets(LIST")
Column A Detroit, MI
Column B 6/4/2007
Column C R1001
Column D Failed
Column E OC
Column F DTRTMI
Column G dtrai1
Column H 200 gold street
Column I 13-1001
Column J 1001OC_DTRT
Column K 13-1001/USSS
Thanks,
Croeg
With help from this site, I've created a form with a listbox that when clicked will populate multiple textboxes on the form in relation to what is highlighted in the listbox. Code below:
Private Sub UserForm_Initialize()
ListBox1.RowSource = "=OFFSET(LIST!$A$2,0,0,COUNTA(LIST!$A:$A),COUNTA(LIST!$1:$2))"
End Sub
Private Sub ListBox1_Click()
Dim c
With ActiveSheet.Range("H:H")
Set c = .Find(ListBox1, LookIn:=xlValues)
wb.Visible = False
If Not c Is Nothing Then
TextBox1 = c.Offset(0, 0).Value
TextBox2 = c.Offset(0, -1).Value
TextBox3 = c.Offset(0, 1).Value
Label27 = c.Offset(0, -5).Value
TextBox7 = c.Offset(0, 5).Value
TextBox10 = c.Offset(0, 3).Value
TextBox14 = c.Offset(0, 4).Value
TextBox15 = c.Offset(0, -2).Value
End If
End With
End sub
The trouble I am running into is that the code seems to filter data. I would like to be able to scroll down the listbox and view the offset data exactly as it is on the spreadsheet (without filtering).
I hope I've provided enough data to help.
Below is the type of data in each column of Sheets(LIST")
Column A Detroit, MI
Column B 6/4/2007
Column C R1001
Column D Failed
Column E OC
Column F DTRTMI
Column G dtrai1
Column H 200 gold street
Column I 13-1001
Column J 1001OC_DTRT
Column K 13-1001/USSS
Thanks,
Croeg