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