VBA code very slow

[HYKE]

Hi Again,

I have this code for some reason I don't know is very slow to respond. I have placed the application.screenupdating as I was told will make the code run faster, however nothing changes on the code response time. Everytime I use the combobox it takes at least 45 seconds for the code to respond and hour glass to dis-appear. Is there something wrong with the code syntax? Kindly advise.

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Select Case ComboBox1
    Case "BPME"
    Dim bpmeCont As Range
    Dim blkchk As Range
    Dim Bs As Worksheet
    Set Bs = Worksheets("BPME")
    Set Bc = Worksheets("BPME BULK CHECK")
    For Each bpmeCont In Bs.Range("bpmeCont")
            With Me.shptUpdate
                 .AddItem bpmeCont.Value
               
            End With
    
    Next bpmeCont
    For Each blkchk In Bc.Range("blkchk")
            With Me.BulkCheck
                 .AddItem blkchk.Value
             
            End With
    
    Next blkchk
    Worksheets("BPME").Select
    Case "EXXONMOBIL"
    Dim exCont As Range
    Dim emBlk As Range
    Dim Ms As Worksheet
    Set Ms = Worksheets("EXXONMOBIL")
    Set Mc = Worksheets("EXXONMOBIL BULK CHECK")
    
    For Each exCont In Ms.Range("exCont")
            With Me.exUpdate
                 .AddItem exCont.Value
           
            End With
    
    Next exCont
    For Each emBlk In Mc.Range("emBlk")
            With Me.BulkCheck
                 .AddItem emBlk.Value
       
            End With
    
    Next emBlk
    Worksheets("EXXONMOBIL").Select
    
    Case "EMARAT"
    Dim emCont As Range
    Dim eMchk As Range
    Dim Es As Worksheet
    Set Es = Worksheets("EMARAT")
    Set Ec = Worksheets("EMARAT BULK CHECK")
  
    For Each emCont In Es.Range("emCont")
            With Me.emUpdate
                 .AddItem emCont.Value
           
            End With
    
    Next emCont
    For Each eMchk In Ec.Range("eMchk")
            With Me.BulkCheck
                 .AddItem eMchk.Value
           
            End With
    
    Next eMchk
    Worksheets("EMARAT").Select
End Select
    Application.ScreenUpdating = True
End Sub

Thanks,

HYKE

 

[Norie]

HYKE

Why are you looping to populate the comboboxes?

Try this.

Option Explicit
 
Private Sub ComboBox1_Change()
Dim rngCont As Range
Dim rngBulk As Range
 
    Application.ScreenUpdating = False
    
    Select Case ComboBox1
        Case "BPME"
        
            Set rngCont = Range("bpmeCont")
            Set rngBulk = Range("bpmeChk")
            Application.Goto Worksheets("BPME").Range("A1")
 
        Case "EXXONMOBIL"
            Set rngCont = Range("exCont")
            Set rngBulk = Range("exChk")
            Application.Goto Worksheets("EXXONMOBILE").Range("A1")
 
        Case "EMARAT"
            Set rngCont = Range("emCont")
            Set rngBulk = Range("emChk")
            Application.Goto Worksheets("EMARAT").Range("A1")
            
    End Select
    
    If Not rngCont Is Nothing And Not rngCont Is Nothing Then
        Me.shptUpdate.List = rngCont.Value
        Me.BulkCheck.List = rngBulk.Value
    End If
 
    Application.ScreenUpdating = True

End Sub

Note when testing I changed the names of the ranges so it's more consistent.


bpmeCont, emCont, exCont are for the BPME sheet, the EMARAT sheet and the EXXON MOBILE sheet respectively.

Similarly for bpmeChk, emChk and exChk.

If that naming pattern is used contistently the posted code could be reduced to only a few lines.

 

[HYKE]

Hi Norie,

You have got a wonderful code here! The code now responds on real time. and yes, this "bpmeChk, emChk and exChk" is used consistently.

THank you once again for your time and for the wonderful code you have provided.


Thanks,

HYKE