Add Range To ListBox UserForm

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi Can anyone help with adding Range Value to List Box UserForm?

I have a User Form which already has a Combo box that adds a list of names From Sheet(A) Using Private Sub UserForm_Initialize()

I have added a List Box and need to add a Range Value From Sheet(B) "W1:AA10" to show in the List Box

ie Sheet(B)

[TABLE="width: 456"]
<tbody>[TR="bgcolor: transparent"]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ref[/TD]
[TD="align: center"]rng[/TD]
[TD="align: center"]Last rng used[/TD]
[TD="align: center"]Available[/TD]
[TD="align: center"]Next[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]21 - 25[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]26-30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]41 - 50[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]51-60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]61 - 75[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]76-90[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]21 - 25[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]26-30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]41 - 50[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]51-60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]61 - 75[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]76-90[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]21 - 25[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]26-30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]41 - 50[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]51-60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]61 - 75[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]76-90[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


The Code I am already using for the Combo Box, the code in red is what I have tried to add for the List Box to show on the User Form


Code:
[LEFT][COLOR=#222222][FONT=Verdana][LEFT][COLOR=#000000][FONT=Calibri]Private Sub [LEFT][COLOR=#000000][FONT=Calibri]UserForm_Initialize()
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Dim wsList As [COLOR=#000000][FONT=Calibri]Worksheet: Set wsList = [/FONT][/COLOR][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri]Worksheets("Sheet(A)")
[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana][LEFT][COLOR=#ff0000][FONT=Verdana][LEFT][COLOR=#ff0000][FONT=Calibri]Dim wsRng As [COLOR=#ff0000][FONT=Calibri]Worksheet: Set [/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]wsRng [/FONT][/COLOR][/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri]= [/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]Worksheets[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri]("Sheet(B)")[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][LEFT][COLOR=#000000][FONT=Calibri][LEFT][COLOR=#000000][FONT=Calibri][LEFT][COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Dim lRow As Long, i As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim plyrs
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][LEFT][LEFT][LEFT][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]Dim NxtRng

[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR][/COLOR][/LEFT]
[/LEFT]

[COLOR=#222222][COLOR=#000000][FONT=Calibri][LEFT][COLOR=#000000][FONT=Calibri]Me.Height = 157.5

[COLOR=#000000][FONT=Calibri]'Auto populate Player Combo[COLOR=#000000][FONT=Calibri]Box[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/LEFT]
        [LEFT][COLOR=#000000][FONT=Calibri]lRow = wsList.Cells[COLOR=#000000][FONT=Calibri](Rows.Count, "M").End[/FONT][/COLOR][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri](xlUp).Row[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/LEFT]

[/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#000000][FONT=Calibri]plyrs = wsList.Range[/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#000000][FONT=Calibri]("M2:M" & lRow)
[/FONT][/COLOR][/COLOR][LEFT][LEFT]
[COLOR=#222222][COLOR=#000000][FONT=Calibri]For i = LBound(plyrs) [/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#000000][FONT=Calibri]To UBound(plyrs)
[/FONT][/COLOR][/COLOR][LEFT][LEFT][LEFT][COLOR=#222222][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri]With [/FONT][/COLOR][/FONT][/COLOR][/COLOR][LEFT][COLOR=#222222][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri]Me.cbPlayerName                    [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/COLOR][LEFT][COLOR=#222222][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri].AddItem plyrs(i, 1)
End With
Next
[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/COLOR]
[LEFT][LEFT][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]NxtRng = wsRng.Range[COLOR=#ff0000][FONT=Calibri]("W1:Z10").Value

[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]With Me.lbNxtRng[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][LEFT][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]          [COLOR=#ff0000][FONT=Calibri]AddItem NxtRng[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[/LEFT]


[COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri][COLOR=#ff0000][FONT=Calibri]End With
[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri][COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR]
[/FONT][/COLOR][/FONT][/COLOR][/COLOR][/LEFT]
[/LEFT]
[/LEFT]

[/LEFT]
[/LEFT]
[/LEFT]
[/LEFT]
[/LEFT]
[/LEFT]
[/LEFT]
[/LEFT]

Any help would be appreciated

Regards

pwill
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try
Code:
   Me.lbNxtRng.List = wsRng.Range("W1:AA10").Value
 
Upvote 0
Thanks Fluff, that has partially worked it has give me a list for the Name Column "W1:W10" but I am trying to include the other columns "X1:AA10" to show in the list box aswell so the values on Sheet(B) "W1:AA10" look the same in list box as on the worksheet ' without the grid lines obviously'

Do I need to change the setting for the actual list box to show multiple columns?

regards
Try
Code:
   Me.lbNxtRng.List = wsRng.Range("W1:AA10").Value
 
Last edited:
Upvote 0
Try this
Code:
   With Me.lbNxtRng
      .ColumnCount = 5
      .List = wsRng.Range("W1:AA10").Value
   End With
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top