Excel Worksheet Events

On my website there are sample Excel files, many of which use event programming to make things happen. Typing something in a cell, selecting a different cell, activating a worksheet, and many other actions, are Events. Add a bit of code in the background, and things can happen automatically if an event occurs. No need to click a button to run a macro, just change a cell, or refresh a pivot table, and the code for that event will run.

For example, on this worksheet there’s a data validation drop down list in cell B2, where a day of the week can be selected.

DataValDays

When you select a weekday, that changes the worksheet, and could trigger a Worksheet_Change event.

In this example, the Worksheet_Change event code checks the address of the cell that was changed, to see if it was cell B2. If it was, a message is displayed.

EventMessage

View the Worksheet Code

To see the code for a worksheet, right-click on its sheet tab, and click View Code.

ViewCode

The Visual Basic Editor opens, and shows the code for the active sheet.

ViewCodeMsg

Multiple Worksheet Events

That code works so well, that you’d like to add another Worksheet_Change event. If you make a change in cell B4, you’d like the date automatically entered in cell B5. Unfortunately, if you add another Worksheet_Change event, you’ll see an error message when you change a cell, because you’ve used the same procedure name twice on the worksheet.

Combine the Code

Instead of creating separate events with the same name, combine both pieces of code into one. For example, you could use Select Case, and specify what should happen when specific cells are changed.

Some situations will require a more complex solution, and if you experiment a bit, you should be able to include multiple outcomes within a single worksheet event’s code.

______________________

You may also like...

5 Responses

  1. Gordon says:

    A warning though for those enticed by the above; monitoring multiple ranges through the worksheet_change event can lead to drastically poorer performance if your sheet is large and/or calculation intensive!

  2. Kobus says:

    A bit misleading here. The Ambiguous Name compile error is because you have created 2 subs by the same name, NOT because you are trying to catch more than one change event. The same compile error will occur if you create 2 subs with the name Private Sub Me(), thus a NAME compile error and Not the event.

    You can add as many worksheet_change events as you want to, but as Gordon above says it slows down the sheets performance so use scarcely. The most common mistake with the worksheet_change event is a continuous loop. This can be prevented by setting the event handler to false in the beginning and if target.address is found, do whatever and opt out of target code, set Event Handler to True so change can be made and Exit Sub

    See sample code below

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.Address = Range(“H171”).Address Then
    If Not Target = UCase(Target) Then
    Target = UCase(Target)
    End If
    Range(“K171”).Value = vbNullString
    GoTo EventHandler
    End If

    If Target.Address = Range(“K171”).Address Then
    If Not Target = UCase(Target) Then
    Target = UCase(Target)
    End If
    Range(“H171”).Value = vbNullString
    GoTo EventHandler
    End If

    EventHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

    Incidentally the part code is copied from a worksheet as a fillable form monitoring 64 change_events without noticing performance slowdown- no calculations

  3. Thanks Gordon and Kobus — I appreciate your comments and thanks for the sample code.

  4. Stan Scott says:

    Sometimes, you need to know whether the changed cell is within a range of cells. Here, the Intersect method comes in handy:

    Private Sub Worksheet_Change(ByVal Target as Range)
    On Error Resume Next

    If Not Application.Intersect(Target, ActiveSheet.Range(“TestRange”)) Is Nothing Then
    ‘do some test here
    End If

    End Sub

  5. Kprajath says:

    i am interested to know, in place of a drop down list box, if i am using a combo list box or active x control box, what’s the trick to monitor & trigger a macro on a change event

Leave a Reply

Your email address will not be published. Required fields are marked *