Listbox rowsource refresh when a line in listbox is selected.

ape · Sep 16th 2019

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 :-

Code

Public Sub SystemNum_Change() 'Combobox
'Set Listbox RowSource <--- "Display selected sheet"


If Sheets("storage").Range("w9").Value = 0 Then
DialogueBox.Value = "Data bank empty"
Else
'Loop through worksheets to find the matching sheet number
Sheets("storage").Range("x10").Value = SystemNum.Value
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name = "Variants" Then
GoTo NextSheets
ElseIf Ws.Name = "storage" Then
GoTo NextSheets
ElseIf Ws.Name = "Data2" Then
GoTo NextSheets
ElseIf Ws.Name = "Help" Then
GoTo NextSheets
ElseIf Ws.Name = "Master" Then
GoTo NextSheets
ElseIf Ws.Name = "manifold" Then
GoTo NextSheets
ElseIf Ws.Name = "Blank" Then
GoTo NextSheets


Else
If Ws.Cells(1, 6).Value = Sheets("storage").Range("x10").Value Then 'Match sheet number
DialogueBox.Value = "System configuration No." & Ws.Cells(1, 6).Value & " selected. Electrical requirements is displayed in list box below."
Me.ListData.RowSource = Ws.Range("G1").Value 'New Rowsource
SysIndex = Ws.Name
End If
End If
NextSheets:
Next Ws
End If


End Sub


This doesn't:-
Public Sub ListData_Click() 'List box


Dim ListItemNumber As Integer


With Me.ListData
For ListItemNumber = 0 To .ListCount - 1
If ListItemNumber > 0 Then
If .Selected(ListItemNumber) Then
Sheets("storage").Range("x10").Value = ListItemNumber
GoTo ItemSelected
End If
End If
Next ListItemNumber
End With
ItemSelected:
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name = "Variants" Then
GoTo NextSheets
ElseIf Ws.Name = "storage" Then
GoTo NextSheets
ElseIf Ws.Name = "Data2" Then
GoTo NextSheets
ElseIf Ws.Name = "Help" Then
GoTo NextSheets
ElseIf Ws.Name = "Master" Then
GoTo NextSheets
ElseIf Ws.Name = "manifold" Then
GoTo NextSheets
ElseIf Ws.Name = "Blank" Then
GoTo NextSheets


Else
If Ws.Cells(1, 6).Value = Sheets("storage").Range("X10").Value Then ' Match sheet number
DialogueBox.Value = "System configuration No." & Ws.Cells(1, 6).Value & " selected. Electrical requirements is displayed in list box below."
Me.ListData.Selected(Sheets("storage").Range("X10").Value) = False ' Deselect
Me.ListData.RowSource = vbNullString '
Me.ListData.RowSource = Ws.Range("G1").Value ' New Rowsource
'Or --- Me.ListData.RowSource = "'" & Ws.Name & "'!G1"
SysIndex = Ws.Name
End If
End If
NextSheets:
Next Ws


End Sub

Display More

**Kenneth Hobson** · Sep 17th 2019

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"):

Code

ListData.RowSource = ""
ListData.RowSource = Ws.Range("G1:H20").Address(external:=True)

ape · Sep 17th 2019

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!

**royUK** · Sep 17th 2019

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?

Code

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

Code

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

If I understand your code then this should be better

Code

Public Sub SystemNum_Change()    'Combobox
'Set Listbox RowSource <--- "Display selected sheet"


    If Sheets("storage").Range("w9").Value = 0 Then
        DialogueBox.Value = "Data bank empty"
    Else
        'Loop through worksheets to find the matching sheet number
        Sheets("storage").Range("x10").Value = SystemNum.Value
        For Each Ws In ThisWorkbook.Worksheets
          Select Case Ws.Name
          Case "Variants", "storage", "Data2", "Help", "Master", "manifold", "Blank"
          Case Else
                If Ws.Cells(1, 6).Value = Sheets("storage").Range("x10").Value Then    'Match sheet number
                    DialogueBox.Value = "System configuration No." & Ws.Cells(1, 6).Value _
                    & " selected. Electrical requirements is displayed in list box below."
                    Me.ListData.List = Ws.Range("G1").Value    'New Rowsource
                    SysIndex = Ws.Name
                End If
            End If
        Next Ws
    End If
End Sub




Public Sub ListData_Click()    'List box


    Dim ListItemNumber As Integer


    With Me.ListData
        For ListItemNumber = 0 To .ListCount - 1
            If ListItemNumber > 0 Then
                If .Selected(ListItemNumber) Then
                    Sheets("storage").Range("x10").Value = ListItemNumber
                  Exit For
                End If
            End If
        Next ListItemNumber
    End With


      For Each Ws In ThisWorkbook.Worksheets
          Select Case Ws.Name
          Case "Variants", "storage", "Data2", "Help", "Master", "manifold", "Blank"
          Case Else
            If Ws.Cells(1, 6).Value = Sheets("storage").Range("X10").Value Then    ' Match sheet number
                DialogueBox.Value = "System configuration No." & Ws.Cells(1, 6).Value & _
                " selected. Electrical requirements is displayed in list box below."
                Me.ListData.Selected(Sheets("storage").Range("X10").Value) = False    ' Deselect
                Me.ListData.Clear '
                Me.ListData.List = Ws.Range("G1").Value   ' New Rowsource
                'Or --- Me.ListData.RowSource = "'" & Ws.Name & "'!G1"
                SysIndex = Ws.Name
            End If
        End If
    Next Ws


End Sub

Display More

**Kenneth Hobson** · Sep 17th 2019

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.

Code

Private Sub UserForm_Initialize()
    ListBox1.RowSource = [G1]
End Sub


Private Sub ListBox1_Click()
    If ListBox1.ListIndex >= 0 Then UpdateLB1
End Sub


Private Sub UpdateLB1()
    TextBox1.SetFocus
    With ListBox1
        '.RowSource = ""
        .RowSource = [G2]
        .SetFocus
    End With
End Sub

Display More

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.

ape · Sep 17th 2019

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!!

ape · Sep 17th 2019

just tried this;

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

no luck!

ape · Sep 18th 2019

Sorry sholud read "Name Manager"

**royUK** · Sep 18th 2019

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

Code

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

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

Code

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.

ape · Sep 18th 2019

Hello Roy. Thank you for the suggestions, I had tried both options (.Value and .address) but I get a Run-time error. I have attached sample work book at different stages. I think List Box does not allow RowSource refresh whilst line selection is active.

Thanks

**royUK** · Sep 19th 2019

You've attached a word documnet. I thought the question was for Excel.

Listbox rowsource refresh when a line in listbox is selected.

Participate now!

Share

Users Viewing This Thread