Listbox rowsource refresh when a line in listbox is selected.

  • Hi all
    I have a list box which displays various information depending on what the rowsource is set to. Currentley I have a combo box which which is used to select the line number and set the rowsource. However what I would like to do is to set the rowsource when specific lines are selected in the listbox itself. I have tried varioud way but nothing seems to update the list! Any help will be appreciate.

    Here is the code;
    This works :-

  • Please paste code between code tags. Click the # icon on reply toolbar to insert the tags.

    I am guessing that it is due to the value of G1. That should be an address string. You can include the sheet in it as you did for the commented G1 line.

    When the rowsource is not the activesheet, if you have a range object, add this option to Address. E.g. Say, we want the string address for ws.Range("G1:H20"):

    ListData.RowSource = ""
    ListData.RowSource = Ws.Range("G1:H20").Address(external:=True)
  • Thank you for your comments. Indeed value of G1 is the new Rowsource which is loaded everytime a sheet is selected. I had tried address option but rowsource does not refresh. This only occures if line selection is made within the list box, if this is done from another control (as in case one) there are no issues!

  • Your code seems a bit convoluted, why are you looping through each worksheet?

    What kind of Controls are you using? Are they on a UserForm?

    Me.ListData.RowSource = Ws.Range("G1").Value

    RowSource requires a Range address not a value

    Use, List is generally the best way to load a ListBox

    Me.ListData.List = Ws.Range("G1").Value

    If I understand your code then this should be better

  • As Roy said, List is likely the better way. I do hope that your are using code structure, indents.

    If your rowsource string is not a continuous range, nothing will show. If it is, you can update the list using rowsource method this way.

    In a project where I wanted to interact with the list from multiple sheets, I created a scratch sheet. I could use either List or RowSource then.

  • Thanks Roy & Kenneth. The program has many user defined sheets which is set up from selections from user Form ComboBoxes, data will be entered in these sheets and this forms a data base. Once each sheet is defined; a dyamic variable with data range is setup and stored in maned manager. At the start of the the program data base will be listed in the list box. So the user will be able to select an item and the sheet's contents will be shown in the list box. Dynamic range name for each sheet is stored in G1. So changes to code (apart from Case statement, "it was a long day") does not work. Sheet selection using ComboBox works absolutley fine, it is the line selection from the list box which is not working!
    Even code Me.ListData.RowSource = "" is not clearing the box!!

  • just tried this;

    Me.ListData.RowSource = "'" & Ws.Name & "'!A2:M" & Range("M" & Rows.Count).End(xlUp).Row

    no luck!

  • As I said earlier RowSource needs an address.I suspect your line of code above should be

    Me.ListData.RowSource = ws.Range("A2:M" & ws.Rows.Count).End(xlUp).Address

    Although, I would use the List Property to populate the ListBox.

    Me.ListData.Lis = ws.Range("A2:M" & ws.Rows.Count).End(xlUp).Value

    Also, I'm confused. You seem to be trying to create a database with multiple sheets, that is not a database.

    Why not attach a small example of your workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!