Daph1990
04-27-2018, 05:23 AM
Hi,
I'll start by thanking everyone in advance. So I have a matrix in Excel which essentially shows some category and sub-category areas. When the user clicks on the buttons/dropdowns I want the userform to show the text associated with their selection. i.e. if 'Strategy' is selected then 'Strategy Consultancy' then 'JLP Content' I then want all the different suppliers that fall under those categories to be listed - I've got that bit all working very well (thanks to some help I received on here last year - so thanks!).
Where I am struggling now is that behind every supplier name there would be a number of other bits of information that I would like to be displayed i.e. company address, telephone number etc so I created a separate sheet ('Supplier Data') with all the other columns I'd be interested in capturing and on the original sheet ("JLP Content") I have data validated dropdowns to select the supplier name from the 'Supplier Data' sheet.
I want my results userform to show all the columns info from the 'Supplier Data' sheet based on what supplier name has been selected from the data validation dropdown on sheet 'JLP Content'. I've attached the Excel file to make it all make sense. But here is the formula currently being used (which only returns the supplier name from sheet 'JLP Content'):
Private Sub cbResult_Click()
Dim r As Long, Col As Long, i As Long
Dim Rng As Range, cel As Range
Dim wArea4 As Range
Dim WS As Worksheet
Dim iRes As Integer
Dim strPrompt As String
Dim strTitle As String
Set WS = Worksheets("JLP Content")
With WS
For Each wArea4 In .Range("StrategyConsultancySubC")
If Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 2
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 3
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 4
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 5
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 6
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 7
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 8
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 9
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 10
End If
Next
For Each wArea4 In .Range("ConceptDevSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 11
End If
Next
For Each wArea4 In .Range("DesignArtDirectionSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 12
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 13
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 14
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 15
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 16
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 17
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 18
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 19
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 20
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 21
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 22
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 23
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 24
End If
Next
For Each wArea4 In .Range("CopyWritingSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 25
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 26
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 27
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 28
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 29
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 30
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 31
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 32
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 33
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 34
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 35
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 36
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 37
End If
Next
For Each wArea4 In .Range("PhotographySubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 38
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 39
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 40
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 41
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 42
End If
Next
For Each wArea4 In .Range("StylistsSubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 4 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 43
End If
Next
For Each wArea4 In .Range("SetDesignBuildSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 5 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 44
End If
Next
For Each wArea4 In .Range("VideographySubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 45
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 46
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 47
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 48
End If
Next
For Each wArea4 In .Range("ArtWorkingSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 49
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 50
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 51
End If
Next
For Each wArea4 In .Range("RetouchAndRepSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 52
End If
Next
For Each wArea4 In .Range("AudioAndVisSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 53
End If
Next
For Each wArea4 In .Range("DigitalBuildSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 54
End If
Next
For Each wArea4 In .Range("QuickTAroundSubC")
If Me.cboArea.ListIndex = 3 And cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 55
End If
Next
End With
On Error Resume Next
Col = Cells(1, Columns.Count).End(xlToLeft).Column - 4
r = ResultsScreen.rowCount.Value
Set Rng = Cells(r, 4).Resize(, Col).SpecialCells(xlCellTypeConstants)
If Err Then
Me.Show
ResultsScreen.Hide
'User Prompt
strPrompt = "No supplier found."
'Messagebox Title
strTitle = "Error!"
'Messagebox Display
iRes = MsgBox(strPrompt, vbExclamation + vbOKOnly, strTitle)
Else
Me.Hide
ResultsScreen.Show
ResultsScreen.lbResults.Clear
End If
For Each cel In Rng
With lbResults
.AddItem Cells(1, cel.Column)
.List(.ListCount - 1, 1) = cel
End With
Next
End Sub
Thank you in advanced :)
I'll start by thanking everyone in advance. So I have a matrix in Excel which essentially shows some category and sub-category areas. When the user clicks on the buttons/dropdowns I want the userform to show the text associated with their selection. i.e. if 'Strategy' is selected then 'Strategy Consultancy' then 'JLP Content' I then want all the different suppliers that fall under those categories to be listed - I've got that bit all working very well (thanks to some help I received on here last year - so thanks!).
Where I am struggling now is that behind every supplier name there would be a number of other bits of information that I would like to be displayed i.e. company address, telephone number etc so I created a separate sheet ('Supplier Data') with all the other columns I'd be interested in capturing and on the original sheet ("JLP Content") I have data validated dropdowns to select the supplier name from the 'Supplier Data' sheet.
I want my results userform to show all the columns info from the 'Supplier Data' sheet based on what supplier name has been selected from the data validation dropdown on sheet 'JLP Content'. I've attached the Excel file to make it all make sense. But here is the formula currently being used (which only returns the supplier name from sheet 'JLP Content'):
Private Sub cbResult_Click()
Dim r As Long, Col As Long, i As Long
Dim Rng As Range, cel As Range
Dim wArea4 As Range
Dim WS As Worksheet
Dim iRes As Integer
Dim strPrompt As String
Dim strTitle As String
Set WS = Worksheets("JLP Content")
With WS
For Each wArea4 In .Range("StrategyConsultancySubC")
If Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 2
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 3
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 4
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 5
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 6
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 7
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 8
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 9
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 10
End If
Next
For Each wArea4 In .Range("ConceptDevSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 11
End If
Next
For Each wArea4 In .Range("DesignArtDirectionSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 12
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 13
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 14
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 15
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 16
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 17
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 18
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 19
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 20
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 21
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 22
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 23
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 24
End If
Next
For Each wArea4 In .Range("CopyWritingSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 25
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 26
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 27
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 28
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 29
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 30
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 31
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 32
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 33
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 34
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 35
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 36
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 37
End If
Next
For Each wArea4 In .Range("PhotographySubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 38
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 39
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 40
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 41
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 42
End If
Next
For Each wArea4 In .Range("StylistsSubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 4 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 43
End If
Next
For Each wArea4 In .Range("SetDesignBuildSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 5 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 44
End If
Next
For Each wArea4 In .Range("VideographySubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 45
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 46
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 47
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 48
End If
Next
For Each wArea4 In .Range("ArtWorkingSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 49
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 50
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 51
End If
Next
For Each wArea4 In .Range("RetouchAndRepSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 52
End If
Next
For Each wArea4 In .Range("AudioAndVisSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 53
End If
Next
For Each wArea4 In .Range("DigitalBuildSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 54
End If
Next
For Each wArea4 In .Range("QuickTAroundSubC")
If Me.cboArea.ListIndex = 3 And cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 55
End If
Next
End With
On Error Resume Next
Col = Cells(1, Columns.Count).End(xlToLeft).Column - 4
r = ResultsScreen.rowCount.Value
Set Rng = Cells(r, 4).Resize(, Col).SpecialCells(xlCellTypeConstants)
If Err Then
Me.Show
ResultsScreen.Hide
'User Prompt
strPrompt = "No supplier found."
'Messagebox Title
strTitle = "Error!"
'Messagebox Display
iRes = MsgBox(strPrompt, vbExclamation + vbOKOnly, strTitle)
Else
Me.Hide
ResultsScreen.Show
ResultsScreen.lbResults.Clear
End If
For Each cel In Rng
With lbResults
.AddItem Cells(1, cel.Column)
.List(.ListCount - 1, 1) = cel
End With
Next
End Sub
Thank you in advanced :)