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)​

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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Sorry that should be "W1:AA10" not "W1:Z10"

regards
 
Last edited:
Upvote 0
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
or do I need to use something else to show these values in the User Form? Maybe?
 
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
189
togenmoser
togenmoser
S
  • Solved
VBA code for userform , to add data to specific cells based on combolistbox selection
Replies
6
Views
618
sloany101
S
S
  • Solved
Make macro smaller
Replies
4
Views
445
Fluff
Fluff
M
  • Question
Pivot table? List? Advanced
Replies
1
Views
226
DanteAmor
DanteAmor
M
  • Question
work
Replies
1
Views
247
etaf
etaf

Forum statistics

Threads
1,214,591
Messages
6,120,425
Members
448,961
Latest member
nzskater

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