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!!!

 

[Norie]

The code for putting the values on the worksheet looks ok, the only thing I can think of is that somehow the correct listindex isn't getting picked up somehow.

That would only happen if the selected value changed from what it was when you populated the textboxes.

Try stepping through with F8 so you can see what's actually happening.

Before you do that set breakpoints (F9) on the first line of code in the click event, that'll make sure you you can start stepping through at the right place/time.

What exactly is the problem with the updated values in the listbox?

Are they completely wrong or just incorrectly formatted?

 

[NeedSomeSeriousHelp77]

Thank you, i'll give the F8 function a try.

In regards to the listbox, this is whats happening.

1. I select the worksheet in the combobox, and it gives me all the row entries in the listbox from that particular worksheet.

I then select a row entry. At this point it shows everything correctly in the textboxes from the row entry I have selected, but the listbox only shows three of the values. It shows the worksheet, the index (from column A) and a value from column L. I would like it to show the values from A,I,J,K and L - I believe the issue lies in the cmdAdd_Click code.

2. When I enter values in the text boxes, and "update" the records, it then only makes 1 change to the listbox - it overwrites the index value (from column A) to the value from L.

 

[Norie]

When you select an item in the listbox it only shows 3 values?

Did it show all the values previously?

 

[NeedSomeSeriousHelp77]

Thats correct, only shows 3 values, and yes, it did seem to show all the values previously, but of course, I was only able to pull up one row previously so it was hardcoded to show specific cells in the listbox.

 

[Norie]

Can I just clarify, when you select an item in the listbox then all but 3 values disappear though the textboxes get populated correctly?

Have you made any changes to the code I posted?

Can you post the code you are using?

 

[NeedSomeSeriousHelp77]

That's correct, when I run the code, all but three values dissapear. The three that rename are the name of the worksheet, the index value, and the value from the last column.

I don't believe i've made any changes, here's the complete code.

Thanks again.
------------------------------------------------------
Option Explicit

Private Sub ComboBox1_Change()

Dim ws As Worksheet
Dim rng As Range
Dim I As Long

' check item has been selected
If ComboBox1.ListIndex <> -1 Then

ListBox1.Clear

Set ws = Worksheets(ComboBox1.Value)

Set rng = ws.Range("A2")

While rng.Value <> ""

ListBox1.AddItem (ws.Name)

ListBox1.List(ListBox1.ListCount - 1, 1) = rng.Value
For I = 2 To 6
ListBox1.List(ListBox1.ListCount - 1, 2) = rng.Offset(, I + 5).Value
Next I

Set rng = rng.Offset(1)

Wend
End If
End Sub

-------------------------------------------------

Private Sub UserForm_Initialize()
Dim ws As Worksheet

ListBox1.ColumnCount = 6

For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next ws

End Sub
--------------------------------------------------

Private Sub cmdAdd_Click()

If txtisc.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("H" & ListBox1.ListIndex + 2).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("I" & ListBox1.ListIndex + 2).Value = txtisc.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("J" & ListBox1.ListIndex + 2).Value = txtisc.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("K" & ListBox1.ListIndex + 2).Value = txtisc.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("L" & ListBox1.ListIndex + 2).Value = txtisc.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value ' Update Listbox with new value
End If
End Sub

-------------------------------------------------------------

Private Sub ListBox1_Click()

txtINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = Sheets(ListBox1.Value).Range("H" & ListBox1.ListIndex + 2).Value
txthandoff.Value = Sheets(ListBox1.Value).Range("I" & ListBox1.ListIndex + 2).Value
txtcanada.Value = Sheets(ListBox1.Value).Range("J" & ListBox1.ListIndex + 2).Value
txtcomplete.Value = Sheets(ListBox1.Value).Range("K" & ListBox1.ListIndex + 2).Value
txtsub.Value = Sheets(ListBox1.Value).Range("L" & ListBox1.ListIndex + 2).Value
' Update TextBox1 with selected A1 value
End Sub

 

[Norie]

Sorry this still isn't clear.

When exactly do the values disappear?

When you run what code?

There's nothing in the code that would/could make that happen.

The only problem might be with the code to populate the listbox but that's run when you select a worksheet from the dropdown, not an item in the listbox.

 

[NeedSomeSeriousHelp77]

Okay, I did some testing of the code, and I think I have a better idea how to explain.

Nothing is dissapearing per say, but whats happening is that it seems like the combobox change code is only asking the listbox to show three things, the worksheet value, the index value (A), and then in the final column its pulling the value from the last column (column L) .

So for example, the code:

For I = 2 To 6
ListBox1.List(ListBox1.ListCount - 1, 2) = rng.Offset(, I + 5).Value
Next I

This will show the value from I=6 (L column) as the third value in the listbox
If I changed this to I = 2 To 5, it will show the value from I=5 (K column) as the third value in the list box.
If changed to I = 2 To 2, it will show the vlaue from I=2 (H column).

So really whats happening here is that the results are overlapping, and the listbox is only showing the final result. I hope this makes more sense.

What I need to do is, instead of having the results overlap, and only showing the final result, I would prefer it to show all or none of the results from all the columns. I can change the code to have no results to show, but Im not sure if this will impact the rest of the code. I am unable to change the code to have all the results show.

 

[Norie]

That's typo on my part.

In the loop this:

ListBox1.List(ListBox1.ListCount - 1, 2)

Should be this:

ListBox1.List(ListBox1.ListCount - 1, I)

 

[NeedSomeSeriousHelp77]

I should have caught that too, sorry to have wasted your time on such a remedial issue.

I'm going to try and work through the pasting to cells issue tonight, I wanted to thank you again for all your help, it's greatly appreciated.