Add Range To ListBox UserForm

P

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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
Fluff said:
Try
Code: 
   Me.lbNxtRng.List = wsRng.Range("W1:AA10").Value
Click to expand...
 
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
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
724
togenmoser
togenmoser
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman
Z
  • Solved
populate data based on multiple criteria
Replies
7
Views
322
Cubist
Cubist
S
  • Solved
VBA code for userform , to add data to specific cells based on combolistbox selection
Replies
6
Views
813
sloany101
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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