Using Combobox on UserForm

[NeedSomeSeriousHelp77]

Hi,

I was hoping to get some help with my VBA code in Excel 2003. To explain what I'm trying to do, I have 5 worksheets in a workbook, each worksheet has 14 identical column headings. I am working with the data from 6 of these columns. One of these 6 columns contains a unique numerical indentifier (column A) under the "INDEX" heading.

I am using a Userform to populate the values from the worksheet, from 5/6 columns (in textboxes), as 1/6 columns should be the "INDEX" column, which I would want displayed in a combo box.

My goal is to select the worksheet, then select the INDEX number in a drop down, and to have the row data from the 5/6 populated in the textboxes. I then want to be able to update the textboxes and translate those updates back to the original worksheets.

Where i'm running into problems is, currently I am using a listbox, which only shows the first row values from the 6 columns, and the combobox drop down is only showing the first value and not the range.

Private Sub UserForm_Initialize()
Dim ws As Worksheet

ListBox1.ColumnCount = 6 ' Listbox with six columns of values

For Each ws In Worksheets
ListBox1.AddItem (ws.Name)
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value
ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Range("H2").Value
ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Range("I2").Value
ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Range("J2").Value
ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Range("K2").Value
ListBox1.List(ListBox1.ListCount - 1, 6) = ws.Range("L2").Value
Next ws

End Sub

Once this is corrected to operate as desired, the rest of my code seems to be working okay, it is as follows;

Private Sub cmdAdd_Click()
If txtisc.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("H2").Value = txtisc.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtisc.Value ' Update Listbox with new value
End If
If txthandoff.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("I2").Value = txthandoff.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txthandoff.Value ' Update Listbox with new value
End If
If txtcanada.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("J2").Value = txtcanada.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcanada.Value ' Update Listbox with new value
End If
If txtcomplete.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("K2").Value = txtcomplete.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcomplete.Value ' Update Listbox with new value
End If
If txtsub.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("L2").Value = txtsub.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value ' Update Listbox with new value
End If
End Sub

Private Sub ListBox1_Click()
cboINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = ListBox1.List(ListBox1.ListIndex, 2)
txthandoff.Value = ListBox1.List(ListBox1.ListIndex, 3)
txtcanada.Value = ListBox1.List(ListBox1.ListIndex, 4)
txtcomplete.Value = ListBox1.List(ListBox1.ListIndex, 5)
txtsub.Value = ListBox1.List(ListBox1.ListIndex, 6)

' Update TextBox with selected value
End Sub

Let me know if any other information would be helpful, and thank you!!!

 

[NeedSomeSeriousHelp77]

Hey Norie,

I wanted to thank you again for all your help. I had to change a few cell references in some of the code, so now everything seems to work correctly.

I appreciate you volunteering your time to help me with my issue even though you really had no stake in it.

Thank you!