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)

WXYZAAABAC
1RefrngLast rng usedAvailableNext
20521 - 2510026-30
301041 - 5010051-60
401561 - 7510076-90
51521 - 2520026-30
611041 - 5020051-60
711561 - 7520076-90
8-1521 - 2530026-30
9-11041 - 5030051-60
10-11561 - 7530076-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


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:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Sorry that should be "W1:AA10" not "W1:Z10"

regards
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
   Me.lbNxtRng.List = wsRng.Range("W1:AA10").Value
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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:

pwill

Active Member
Joined
Nov 22, 2015
Messages
406

ADVERTISEMENT

or do I need to use something else to show these values in the User Form? Maybe?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Try this
Code:
   With Me.lbNxtRng
      .ColumnCount = 5
      .List = wsRng.Range("W1:AA10").Value
   End With
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Thanks Fluff, that's done trick much appreciated :)

Regards

pwill
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,293
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top