how to distinguish between manual cell change and automatic cell change on excel vba

Question

I hope you're doing well

I have a sheet in which I have members of project team. It contains two rows : The first one is for the ID and the second one is for the name

When the sheet is activated, the team members are loaded from the database, with the ID and the name.

When I want to add a new member in the team, the process is the fllowing :

1. Write manually the id of the member to add, in the first row
2. The sheet loads the name from another sheet according to the id typed in step 1

For the step 2 , there are two ways to do it :

• Either by writing a formula in the second row, for fixed large number of cells, which extract the name form the other sheet according to the id of the first row.
• Handle change event on the first row using a vba code like this :

private sub Worksheet_change(ByVal target as Range)
    if Not Application.Intersect(target.row, ActiveSheet.rows(1)) Is Nothing then
        Insert new column 'It is important, without this the changes are not applicable
        look for the name of the id ih the target cell and make changes
    End If

The second solution is more dynamic and I prefer this way, however I encountred a problem : This sub is applied also when doing step 1 (loading ID's from the database), making the vba program running without end, and therefore blocking the excel

The question is :

Is there any way to handle user change diffrently from the programmatically change on vba ?

Thank in advance

Answer 1

You want to make sure that the change caused by VBA does not trigger another Worksheet_change event. There is no need to check if the original event was caused by a person or the program. All you need to do is prevent an infinite loop. This is something that you will need to keep in mind every time you use the Worksheet_change event.

You can do this by first disabling events as the Worksheet_change event starts, and then re-enabling events at the end.

Private sub Worksheet_change(ByVal target as Range)

    Application.EnableEvents = False ' Disable events to prevent infinite loop

    If Not Application.Intersect(target.row, ActiveSheet.rows(1)) Is Nothing then
        Insert new column 'It is important, without this the changes are not applicable
        look for the name of the id in the target cell and make changes
    End If

    Application.EnableEvents = True ' Re-enable events

End Sub

Therefore, whenever a person changes something in the sheet, the event is fired. As soon as the event starts, events are disabled which prevents VBA from triggering itself. At the end of the event, the events are re-enabled so that it can respond to another user-caused change.