Worksheet Events In Excel VBA. Excel Training VBA lesson 16

Let's now look at some of the Worksheet Events. These are not a lot different from the Workbook Events except that while the Workbook Events can apply to all Worksheets the Worksheets Events only apply the the Worksheet which houses them.

As with the Workbook Events all these start with the words "Private Sub". Again this means they are only available to the Worksheet Object in which they are housed. These Worksheet Objects can be seen in the "Project Explorer" and have the same name as the Worksheet tab name. You can double click the Worksheet Object to gain access to the Worksheet Module. If you are not in the VBE you can right click on the sheet name tab and select "View Code". In Excel 97 the default will be:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

If you are using Excel 2000 + the default will be blank. You can still save yourself the problem of having to type out the Worksheet Event you want by clicking the drop arrow on the "Object" box (top left of the Module) and selecting "Worksheet". This will then default to:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

In the both versions 97 and 2000 + all the Events for the Worksheet are listed in the "Procedure" box (top right of the Module). Simply select the Event you want and it will be written for you.

As the Workbook_Open is arguably the most popular Workbook Event the Worksheet_Change is the most popular Worksheet Event.

This event is fired whenever a cell on a Worksheet changes. The exception to this is deleting a cell, the Event does not fire then. You can see that this Event takes an argument that is called "Target" and is parsed to the Event as a range. The Target is always the cell that triggered the Event. Let's say that every time a user types a number in cells A1:A10 we need to multiply it by itself.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rWatchRange As Range
On Error GoTo ResetEvents

'Set range variable to A1:A10
Set rWatchRange = Range("A1:A10")

    'The cell they have changed (Target) _
     is within A1:A10
    If Not Application.Intersect _
            (Target, rWatchRange) Is Nothing Then
            'They entered a number
             If IsNumeric(Target.Value) Then
                'Prevent the Event from firing again _
                 when we multiply it(the Target)by itself.
                 Application.EnableEvents = False
                'Multiply it by itself
                 Target.Value = Target.Value * Target.Value
            End If
    End If

ResetEvents:
Application.EnableEvents = True
End Sub

Let's now work our way through the code to see what it does.

This is put in to force Excel to go to the line of code directly below "ResetEvents" should any unexpected errors occur.

The Intersect returns a Range object that represents the rectangular intersection of two or more ranges. We use this to find out whether the cell which triggered the Event (Target) is within A1:A10 (rWatchRange). If the Target is NOT within the range A1:A10 the Intersect Method would return Nothing. The default for a empty Range Object. The "Not" is used to reverse the If statement.

This is very important when using Worksheet Events. If we did not include it we could end up with an endless Loop. This is because we are changing the Target cell which would in turn re-trigger the change event, which would then run our code again and so on…..

The "Application.EnableEvents=True" turns the Events back on so it will run the next time. The "ResetEvents:" is a sign post for our "On Error GoTo". If an unexpected error does occur after Events have been disabled it will at least enable them again.

This Event is fairly straightforward, it fires immediately after the Worksheet calculates. If we had a Worksheet full of formulas that were recalculating frequently we could use this to check whether the recalculated value is a certain cell has reached a certain limit. If it has, we could display a message box telling them to perform some action.

Private Sub Worksheet_Calculate()
    If IsNumeric(Range("A1")) Then
      If Range("A1").Value >= 100 Then
        MsgBox "Range A1 has reached is limit of 100", vbInformation
      End If
    End If

Private Sub Worksheet_BeforeRightClick (ByVal Target As Excel.Range, Cancel As Boolean)

This Event fires immediately before the default action of the Worksheet right mouse click. In the case of the Worksheet that default action is the shortcut popup menu that appears. This Event takes two arguments "Target" and "Cancel". The Target is the same as the Target for the Worksheet ie; it refers the active Range Object. The Cancel refers to the shortcut popup menu, setting it to False will prevent the popup menu from showing. I have used this Event in the past to simply prevent the popup menu from showing when I have hidden all Excels standard Command Bars and replaced them with a customized one. I have also used it to display my own custom popup menu. As this is probably the best use for this Event, let's use it as an example.

Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
Dim cBar As CommandBar
Dim cCon1 As CommandBarControl
Dim cCon2 As CommandBarControl

'Prevent the standard popup showing
Cancel = True

'We must delete the old one first
On Error Resume Next
Application.CommandBars("Ozgrid").Delete

    'Add a CommandBar and set the CommandBar _
     variable to a new popup
     Set cBar = Application.CommandBars.Add _
     (Name:="Ozgrid", Position:=msoBarPopup)

    'Add a control and set our 1st CommandBarControl _
     variable to a new control
     Set cCon1 = cBar.Controls.Add
    'Add some text and assign the Control
    With cCon1
        .Caption = "I'm a custom control"
        .OnAction = "MyMacro"
    End With

    'Add a control and set our 2nd CommandBarControl _
     variable to a new control
     Set cCon2 = cBar.Controls.Add
    'Add some text and assign the Control
    With cCon2
        .Caption = "So am I"
        .Caption = "AnotherMacro"
    End With

cBar.ShowPopup
End Sub

Don't worry too much if you cannot fully understand how the adding Popup works, I only use this as an example because I believe it is the most relevant example. Let's step through it.

As I said above this setting the Cancel argument to True will stop the default popup.

This part is very important! While the code would run ok the very first time we ran it, it would not run the next time or anytime after. This is because a CommandBar called "Ozgrid" would already exist and so cause a run time error. The "On Error Resume Next" will prevent a run time error the first time it tries to delete the Command Bar "Ozgrid", which wouldn't yet exist.

Set cBar = Application.CommandBars.Add _
(Name:="Ozgrid", Position:=msoBarPopup)

Here we are adding a Command Bar calling it "Ozgrid" and setting it's Position to "msoBarPopup". You must do this with a Popup. If it was a normal Command Bar we could Position it at the top of the screen with all other Command Bars. We then set our variable to the new Command Bar.

With cCon1
.Caption = "I'm a custom control"
.OnAction = "MyMacro"
End With

Now we use the Control Object (cCon) and add a Caption, then assign it to a macro called "Mymacro", this would reside in a normal Macro.

This Event occurs whenever the Worksheet is Deactivated. You could use this on worksheet to ensure that the sheet always remained hidden. The user would have to go to Window>Unhide to view the sheet.

Private Sub Worksheet_Deactivate()

Me.Visible = xlSheetHidden

End Sub

Note the use of the keyword "Me" here. As the code is in the Private Module of the Worksheet Object we can refer to the Worksheet with this. The same could be used in all Worksheet Events. If we were dealing with a Workbook Object Event "Me" would always refer to the Workbook itself.

So as you can see we can have any macro or code run whenever a particular Event occurs. This can be a big advantage to the programmer as we do not have to rely on the user to activate it. It can also make life easy for the user as it can do things for them automatically, even without them knowing.

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA Lesson 16

Worksheet Events In Excel VBA

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX