Worksheet Change - ScreenUpdating

[Forestq]

Hi,

I`m using Worksheet_Change to put values in table. I set DisplayAlerts and ScreenUpdating to FALSE.

Application.DisplayAlerts = False
Application.ScreenUpdating = False


    If Target.Address = "$D$9" Then
        For g = 1 To rowdata_lr
               ......
        next g
    End if

Application.DisplayAlerts = TrueApplication.ScreenUpdating = True

But when code is running (after I select value from list in D9), I see like values are showing one-by one, and screen is refreshing.

Can somehow I eleminate this?

 

[ShawnPCooke]

Here's the thing. You're using Worksheet_Change, which is an event that is triggered every time a cell is changed.

What happens when you make a change to D9? It is then changing a bunch of other cells, and each one of those cells is also triggering the event. Part of that means disabling and reenabling screen updating, which means that each change results in a screen refresh.

Try this:

    If Target.Address = "$D$9" Then
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For g = 1 To rowdata_lr
               ......
        next g

        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End If

That way, you are not only disabling updates only when the appropriate cell is triggered, you are also disabling events for the changes you're making yourself. Code should run much faster now, too.

 

[Forestq]

thanks!