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)
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)
W | X | Y | Z | AA | AB | AC | ||
1 | Ref | rng | Last rng used | Available | Next | |||
2 | 0 | 5 | 21 - 25 | 100 | 26-30 | |||
3 | 0 | 10 | 41 - 50 | 100 | 51-60 | |||
4 | 0 | 15 | 61 - 75 | 100 | 76-90 | |||
5 | 1 | 5 | 21 - 25 | 200 | 26-30 | |||
6 | 1 | 10 | 41 - 50 | 200 | 51-60 | |||
7 | 1 | 15 | 61 - 75 | 200 | 76-90 | |||
8 | -1 | 5 | 21 - 25 | 300 | 26-30 | |||
9 | -1 | 10 | 41 - 50 | 300 | 51-60 | |||
10 | -1 | 15 | 61 - 75 | 300 | 76-90 | |||
11 | ||||||||
12 |
<tbody>
</tbody>
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
Any help would be appreciated
Regards
pwill
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: