Listbox on Userform and a worksheet Excel VBA

[DThib]

Arrgh!,

Last code for this complex thing.
My quandary is using the TextBox (SPR_IDTB) from a userform to populate a ListBox on the userform.
If the matching ID (SPR_IDTB) is found, pull the value in the same row from column A and from Column J. End after searching 1 worksheet. TextBox value is in row B.

Best guess at this point:

Sub Reslcomm_TB_Change()
   
    Dim c As Range
    Alcomm_LB.Clear
    For Each c In Sheets("Assigned").Range("B:B").End(xlUp)
        If c.Value = SPR_IDTB.Value Then
            Alcomm_LB.AddItem c.Offset(, 12).Value
            Alcomm_LB.AddItem c.Offset(, -1).Value
            Alcomm_LB.List(Alcomm_LB.ListCount - 1, 1) = c.Offset(, 1).Value
        End If
    Next


End Sub

DThib

 

[My Aswer Is This]

So are you saying the code your showing is not working?
Looks like to me it should work.

Not sure why you have this line of code:

Alcomm_LB.List(Alcomm_LB.ListCount - 1, 1) = c.Offset(, 1).Value
​

 

[Fluff]

The start of the loop should be

    For Each c In Sheets("Assigned").Range("B2", Sheets("Assigned").Range("B" & Rows.Count).End(xlUp))

 

[DThib]

Yes. The rest of the code pulls data into a worksheet and this will be added to. The textbox looks for the match and fills out other textboxes on the userform.
I am trying to have this value also search the rest of the worksheet and where it finds the text in the textbox it should take the row and grab the value in column A and column K to place in the list box

 

[Fluff]

Also if you just want cols A & J in the listbox, on the same line try

        If c.Value = SPR_IDTB.Value Then
            Alcomm_LB.AddItem c.Offset(, -1).Value
            Alcomm_LB.List(Alcomm_LB.ListCount - 1, 1) = c.Offset(, 12).Value
        End If

 

[DThib]

Drats! Foiled again...

It does not work. I am calling this from the larger macro that fills out the textboxes for the current days information.
This would fire next as Call Lister
Does not work.

Sub Lister()
   
    Dim c As Range
     For Each c In Sheets("Resolved").Range("B2", Sheets("Resolved").Range("B" & Rows.Count).End(xlUp))
        If c.Value = SPR_IDTB.Value Then
            Reslcomm_LB.AddItem c.Offset(, -1).Value
            Reslcomm_LB.List(Reslcomm_LB.ListCount - 1, 1) = c.Offset(, 10).Value


        End If
    Next


End Sub

Help

 

[DThib]

Forgot to mention,

The listbox (Reslcomm_LB) has columns.
"A" is the fist column.
"J" is the second column.

DThib

 

[Fluff]

Please define

Does not work.

 

[DThib]

I Call Lister and nothing fills the list box when I have set up 3 instances on the worksheet to pull from.
The code does nothing. Compiling shows no fault.

 

[DThib]

It works now. I had a logic statement stopping it and the beginning of the larger macro.

Thanks Fluff!