Userform1.Listbox1 values automatically update

[Patriot2879]

Hi please can i get some help, i am really struggling i have a listbox1 located in userform1, i would like the listbox1 to update with the row.values from sheet1, please can you help.

 

[DanteAmor]

Use the Rowsource property of the listbox to load the sheet information into the listbox. With this property, any data that you modify in the cell will automatically update the listbox,

For example:

ListBox1.RowSource = "Sheet1!" & Range("A2:D20").Address

 

[Patriot2879]

Use the Rowsource property of the listbox to load the sheet information into the listbox. With this property, any data that you modify in the cell will automatically update the listbox,

For example:

ListBox1.RowSource = "Sheet1!" & Range("A2:D20").Address

HI thank you for your help, please can you advise where i place this code? i have placed it in the below but i get an error. Please advise

Private Sub Listbox1_Click
ListBox1.RowSource = "Sheet1!" & Range("A2:D20").Address
End Sub

 

[Patriot2879]

HI thank you for your help, please can you advise where i place this code? i have placed it in the below but i get an error. Please advise

Private Sub Listbox1_Click
ListBox1.RowSource = "Sheet1!" & Range("A2:D20").Address
End Sub

HI i have added the code to my UserForm_Initialize() but its not working please can you help.

Private Sub UserForm_Initialize()

'Add multiple Columns to a listbox
ListBox1.Clear 'Make sure the Listbox is empty
ListBox1.ColumnCount = 10 'Set the column Amount
'Fill the Listbox
ListBox1.AddItem "Source" 'Additem creates a new row
ListBox1.List(0, 1) = "MPAN" 'List(x,y) X is the row number, Y the column number
ListBox1.List(0, 2) = "Work Stream"
ListBox1.List(0, 3) = "Job Number"
ListBox1.List(0, 4) = "SOD Allocated To"
ListBox1.List(0, 5) = "Post Code"
ListBox1.List(0, 6) = "Job Category"
ListBox1.List(0, 7) = "Appointment Date"
ListBox1.List(0, 8) = "Appointment Time Slot"
ListBox1.List(0, 9) = "Booking Agent"

Dim i As Integer
i = UserForm1.ListBox1.RowSource = "In Day VL!" & Range("A2:D20").Address

 

[DanteAmor]

What I gave you is an example.
You did not put how your data is, nor did you put exactly which cells your data is in.
If you do not provide complete information, we assume that you will be able to adapt the example.

So in order to help you you must give an example of how your data is.

I'll try again, but I don't know which row your headers are in, nor do I know which row your data starts in.
I insist, if you do not provide complete information about your data, I will only be guessing

Replace all your code from initalize event for this:

Private Sub UserForm_Initialize()
  Dim lr As Long
  Dim sh As Worksheet
  
  'set the sheet with data
  Set sh = Sheets("In Day VL")
  
  'get the last row in sheet
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  
  'work with listbox1
  With ListBox1
    .ColumnCount = 10                                                   'Set the column Amount
    .ColumnHeads = True
    .RowSource = "'" & sh.Name & "'!" & sh.Range("A2:J" & lr).Address   'Fill the Listbox
  End With
End Sub

 

[Patriot2879]

What I gave you is an example.
You did not put how your data is, nor did you put exactly which cells your data is in.
If you do not provide complete information, we assume that you will be able to adapt the example.

So in order to help you you must give an example of how your data is.

I'll try again, but I don't know which row your headers are in, nor do I know which row your data starts in.
I insist, if you do not provide complete information about your data, I will only be guessing

Replace all your code from initalize event for this:

Private Sub UserForm_Initialize()
  Dim lr As Long
  Dim sh As Worksheet
 
  'set the sheet with data
  Set sh = Sheets("In Day VL")
 
  'get the last row in sheet
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
 
  'work with listbox1
  With ListBox1
    .ColumnCount = 10                                                   'Set the column Amount
    .ColumnHeads = True
    .RowSource = "'" & sh.Name & "'!" & sh.Range("A2:J" & lr).Address   'Fill the Listbox
  End With
End Sub

That works great thank you so much

 

[Peter_SSs]

That works great thank you so much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

@Patriot2879
I don't think that you are understanding the 'Mark as solution' feature of the forum.
You marked post #6 as the solution when that post does not contain a solution but simply said "That works great thank you so much".
So I changed the 'Mark as solution' to post #5 since Dante's post does contain the solution.
You then changed the mark to post #7 which again clearly does not contain a solution to the question that you asked.
I have again changed the 'Mark as solution' to Dante's post #5. Please do not change it again (unless somebody posts a better solution that post #5)

I also note that you have not updated your profile as requested in the last paragraph of my previous post. People will be able to help you better if they know what Excel version you are using.

 

[Patriot2879]

Thank you for all your help, just updated my profile now

 

[Peter_SSs]

just updated my profile now

Thanks for doing that.