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]

So you want code to populate the listbox with all the data from all the worksheets?

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rng As Range
Dim I As Long

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

For Each ws In Worksheets
 
    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

Next ws

End Sub

By the way, have you considered putting this code in the combobox change event.

So when a worksheet is selected from it the listbox is populated with just the data from the selected worksheet.

Also you will need to change your other code, instead of hardcoding row 2 use the ListIndex property of the listbox to get the correct row for the data to goto.

Remember though, listboxes have a zero-based index, so the first item in the list has the ListIndex 1, the second 2, the third 3 and so on.

 

[NeedSomeSeriousHelp77]

Hi Norie,

Thank you for the suggestions. Unfortunately I'm very very new with this type of coding, so I'll probably need to do this step by step, ie. make this change, then figure out the coding for the rest.

I do like your suggestion about the combo box change event, I just wouldn't know how to go about it.

I know this is asking alot, but if this is something that wouldn't take too much of your time and effort, would you be able show me how you would go about writing this VBA coding?

Thank you kindly,

 

[Norie]

All you would need to do basically is move the code for populating the listbox into the combobox change event and add a couple of things.

To access the change event just double click the combobox in design view.

You should then see something like this.

Private Sub ComboBox1_Change()
 
End Sub

That's where you need to add the code.

The code you need to add is to check something has been selected from the combobox, clear the listbox and set a reference to the worksheet.

' 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 <> ""
 
          ' code to populate
 
       Set rng = rng.Offset(1)
     Wend
End If

 

[NeedSomeSeriousHelp77]

Hey Norie,

I was unsuccessful in populating under the combobox change event.

I followed your instructions, I combined the two codes and placed it in the combobox change event, but I'm obviously missing something as I wasn't getting any results.

At this point I know I'd be asking too much to explain further, as the knowledge is lacking on my end, meaning without asking you to actually write out the entire code, it would probably just go over my head.

I think i'll stick with the first solution, but would you please help me with removing the hardcoding from line 2 and replacing with the listindex feature so that I can update the worksheet?

Thanks again.

 

[Norie]

Sorry I don't quite follow.

What exact code did you add/change that didn't work?

Where did you add code?

For the row replace the hardcoded number with something like this.

Sheets(ListBox1.Value).Range("H" & ListBox1.ListIndex + 2).Value = txtisc.Value ' Update worksheet

 

[NeedSomeSeriousHelp77]

Sorry to be unclear. The Code I entered looked like this;


Private Sub ComboBox1_Change()

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

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

For Each ws In Worksheets

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

Next ws

' 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 <> ""

' code to populate

Set rng = rng.Offset(1)
Wend
End If

End Sub

I moved the code for populating the listbox into the combobox change event and added the code to check if something has been selected from the combobox, clear the listbox and set a reference to the worksheet.

 

[Norie]

Why did you add the original code to the change event?

Probably my fault but I thought I'd made it clear by posting almost the entire code you need.

The only bits missing from the code in post #4 are Sub and End Sub and the declarations of ws and rng.

Try removing everything from 'Dim I As Integer' down to 'Next ws'.......

Wait a minute, I'll just post the code as it should be.

Hopefully that should make things easier/clearer.

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

 

[NeedSomeSeriousHelp77]

You probably did make it clear, I'm just too wet behind the ears to understand.

I appreciate you taking the time to write the code out. I'll give this a try.

Thank you very much for all your help.

 

[NeedSomeSeriousHelp77]

<P>Hi Norie,</P>
<P> </P>
<P>Well thanks to you, the first part of my problem is solved. I spent the last couple days trying things to figure out the second part, but of course, i'm stuck again.</P>
<P> </P>
<P>Where the problem lies is in the code to update the cells in the referenced rows (based on the index).  So when I select an index, it always updates the second row on the spreadsheet regardless of which row the index lies in. Of course thats because the code is currently designed to do that, but I've tried a few things, and can't figure out how to update the appropriate row based on the selection. The second part is, when updates are made to the textboxes, its not showing correctly in the listbox.</P>
<P> </P>
<P>Here's the code, help would be greatly welcomed. Thank you!</P>
<P> </P>
<P>Private Sub cmdAdd_Click()</P>
<P><BR>        If txtisc.Value <> vbNullString Then<BR>        Sheets(ListBox1.Value).Range("H" & ListBox1.ListIndex + 2).Value = txtisc.Value    ' Update worksheet<BR>        ListBox1.List(ListBox1.ListIndex, 1) = txtisc.Value       ' Update Listbox with new value<BR>    End If<BR></P>
<P>    If txthandoff.Value <> vbNullString Then<BR>        Sheets(ListBox1.Value).Range("I" & ListBox1.ListIndex + 2).Value = txtisc.Value    ' Update worksheet<BR>        ListBox1.List(ListBox1.ListIndex, 1) = txthandoff.Value       ' Update Listbox with new value<BR>    End If<BR></P>
<P>    If txtcanada.Value <> vbNullString Then<BR>        Sheets(ListBox1.Value).Range("J" & ListBox1.ListIndex + 2).Value = txtisc.Value    ' Update worksheet<BR>        ListBox1.List(ListBox1.ListIndex, 1) = txtcanada.Value       ' Update Listbox with new value<BR>    End If<BR></P>
<P>    If txtcomplete.Value <> vbNullString Then<BR>        Sheets(ListBox1.Value).Range("K" & ListBox1.ListIndex + 2).Value = txtisc.Value    ' Update worksheet<BR>        ListBox1.List(ListBox1.ListIndex, 1) = txtcomplete.Value       ' Update Listbox with new value<BR>    End If<BR></P>
<P>    If txtsub.Value <> vbNullString Then<BR>        Sheets(ListBox1.Value).Range("L" & ListBox1.ListIndex + 2).Value = txtisc.Value    ' Update worksheet<BR>        ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value       ' Update Listbox with new value<BR>    End If</P>
<P> </P>
<P>End Sub</P>
<P> </P>
<P> </P>
<P>Private Sub ListBox1_Click()<BR></P>
<P>    txtINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)<BR></P>
<P>    txtisc.Value = Sheets(ListBox1.Value).Range("H" & ListBox1.ListIndex + 2).Value<BR></P>
<P>    txthandoff.Value = Sheets(ListBox1.Value).Range("I" & ListBox1.ListIndex + 2).Value<BR></P>
<P>    txtcanada.Value = Sheets(ListBox1.Value).Range("J" & ListBox1.ListIndex + 2).Value<BR></P>
<P>    txtcomplete.Value = Sheets(ListBox1.Value).Range("K" & ListBox1.ListIndex + 2).Value<BR></P>
<P>    txtsub.Value = Sheets(ListBox1.Value).Range("L" & ListBox1.ListIndex + 2).Value<BR></P>
<P>         ' Update TextBox1 with selected A1 value<BR></P>
<P>End Sub<BR></P>
<P> </P>