If you are using Excel 2000+ the default will be blank. Youcan still save yourself the problem of having to type out the Worksheet Eventyou want by clicking the drop arrow on the 'Object' box (topleft of the Module) and selecting 'Worksheet'. This will thendefault to:
Private Sub Worksheet_SelectionChange(ByVal Target AsExcel.Range)
In the both versions 97 and 2000+ all the Events for theWorksheet are listed in the 'Procedure' box (top right of theModule). Simply select the Event you want and it will be written for you.
Lets look at each one of these is turn and put them touse.
As the Workbook_Open is arguably the mostpopular Workbook Event the Worksheet_Change is the most popularWorksheet Event.
Private Sub Worksheet_Change(ByVal Target AsExcel.Range)
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 cansee that this Event takes an argument that is called 'Target'and is parsed to the Event as a range. The Target is always the cellthat triggered the Event. Let's say that every time a user types anumber 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 itdoes.
On Error GoToResetEvents
This is put in to force Excel to go to the line of codedirectly below 'ResetEvents' should any unexpected errors occur.
Set rWatchRange =Range('A1:A10')
This sets our range variable to the Range we are interestedin.
If NotApplication.Intersect(Target, rWatchRange) Is Nothing Then
Excel Vba Calculate Worksheet
The Intersect returns a Range object that represents therectangular intersection of two or more ranges. We use this to find out whetherthe cell which triggered the Event (Target) is withinA1:A10 (rWatchRange). If the Target is NOT within the rangeA1:A10 the Intersect Method would return Nothing. Thedefault for a empty Range Object. The 'Not' is used toreverse the If statement.
If IsNumeric(Target.Value)Then
This will return True if the Target contains a number.
Application.EnableEvents =False
This is very important when using Worksheet Events. If we didnot include it we could end up with an endless Loop. This is because we arechanging the Target cell which would in turn re-trigger the change event, whichwould then run our code again and so on….
Target.Value = Target.Value *Target.Value
Once we finally reach here we can multiply the value of theTarget by itself.
ResetEvents:
Application.EnableEvents = True
The 'Application.EnableEvents=True' turns the Events back onso it will run the next time. The 'ResetEvents:' is a sign post for our'OnError GoTo'. If an unexpected error does occur after Events have been disabledit will at least enable them again.
Private Sub Worksheet_Calculate()
This Event is fairly straightforward, it fires immediatelyafter the Worksheet calculates. If we had a Worksheet full of formulas that wererecalculating frequently we could use this to check whether the recalculatedvalue is a certain cell has reached a certain limit. If it has, we could displaya 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
End Sub
As you can see theWorksheet_Calculate Event takes no arguments.
Private SubWorksheet_BeforeRightClick (ByVal Target AsExcel.Range, Cancel As Boolean) Ableton live 9 multiple users manual mac.
This Event fires immediatelybefore the default action of the Worksheet right mouse click. In the case of theWorksheet that default action is the shortcut popup menu that appears. ThisEvent takes two arguments 'Target' and'Cancel'. The Target is the same as the Target for theWorksheet ie; it refers the active Range Object. The Cancel refers to theshortcut popup menu, setting it to False will prevent the popupmenu from showing. I have used this Event in the past to simply prevent thepopup menu from showing when I have hidden all Excels standard Command Bars andreplaced them with a customized one. I have also used it to display my owncustom popup menu. As this is probably the best use for this Event, let's use itas 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 youcannot fully understand how the adding Popup works, I only use this as anexample because I believe it is the most relevant example. Let's step throughit.
Cancel= True
As I said above this setting theCancel argument to True will stop the default popup.
On Error Resume Next
Application.CommandBars('Ozgrid').Delete
This part is very important!While the code would run ok the very first time we ran it, it would not run thenext 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 timeerror the first time it tries to delete the Command Bar 'Ozgrid', which wouldn'tyet exist.
Set cBar = Application.CommandBars.Add _
(Name:='Ozgrid', Position:=msoBarPopup)
Here we are adding a Command Barcalling it 'Ozgrid' and setting it's Position to 'msoBarPopup'. You must do this with a Popup. If itwas a normal Command Bar we could Position it at the top of the screen with allother Command Bars. We then set our variable to the new Command Bar.
SetcCon1 = cBar.Controls.Add
We now add a Control to our newCommand Bar and set it to a variable.
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 wouldreside in a normal Macro.
cBar.ShowPopup
This simply tells Excel to showour new Popup.
Private SubWorksheet_Deactivate()
This Event occurs whenever theWorksheet is Deactivated. You could use this on worksheet to ensure that thesheet always remained hidden. The user would have to go toWindow>Unhide to view the sheet.
Me.Visible =xlSheetHidden
Note the use of the keyword'Me' here. As the code is in the Private Module of theWorksheet Object we can refer to the Worksheet with this. The same could be usedin all Worksheet Events. If we were dealing with a Workbook Object Event'Me' would always refer to the Workbook itself.
Summary
So as you can see we can have anymacro or code run whenever a particular Event occurs. This can be a bigadvantage to the programmer as we do not have to rely on the user to activateit. It can also make life easy for the user as it can do things for themautomatically, even without them knowing.
<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX
