It's not loading the brands that's taking so much time is all the loops you have in the code for CustVendBox2_Change.
For example, why are you looping to deselect every brand?
Also, you appear to be looping through the brand list 999 times, which is 300,000+ iterations, are you sure you need to do that?
As far as I can see all you shouldn need to do is loop down the customers column and wherever you find 'REQUESTED' use the row number to select the relevant brand, you could also deselect any brands which don't have 'REQUESTED' in the customer column.
Something like this.
Private Sub CustVendBox2_Change()
Dim lngRow As Long
Dim wsCustVend As Worksheet
Dim wsBrand As Worksheet
Dim Brand As String
Dim p As Variant
Dim Col As Integer
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set wsCustVend = Sheets("CustomerVendorList")
Set wsBrand = Sheets("BrandList")
Set rng = wsBrand.Rows("1:1").Find(what:=CustVendBox2)
On Error GoTo Finish:
Col = rng.Column
For rcell = 2 To BrandListBox.ListCount - 1
BrandListBox.Selected(rcell - 2) = wsBrand.Cells(rcell, Col) = "REQUESTED"
Next rcell
Finish:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks