Worksheet Events and Workbook Events

Location: http://www.mvps.org/dmcritchie/excel/event.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Note before continuing

A worksheet_change event will not recognize entry of a date with a short-cut (Ctrl+;), nor will it recognize a change by pasting a value. You must actually key in a change. It is possible to change multiple cells.
A word of warning. Change Event may make things easier, but you can very quickly end up a page full of formatting. Check your lastcell to make sure you are not going to be drastically increasing the size of your workbook.
If you are thinking of using an Event macro to restrict input, you might want to check Data, Validation features.

Worksheet Events

Unlike regular macros which are installed in regular modules, Worksheet Events are installed with the worksheet by rightclicking on the sheettab, choose 'view code', and then paste in your macro.

These are the worksheet events available in Excel, as found in the dropdown on within "View Code" after RClick on a sheet tab.

Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'should be part of Change macro
Application.EnableEvents = True 'should be part of Change macro
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Unlike normal macros the worksheet event macros are installed directly from the worksheet, see comments in some of the macros.
A brief description of the above and other Excel objects can be found in
Microsoft Office 97/Visual Basic Programmer's Guide
Chapter 4 -- Microsoft Excel Objects
http://www.microsoft.com/officedev/articles/Opg/004/004.htm
Tom Ogilvy included a brief summary as as follows in one of his postings:
The Change event will tell you the cell you are leaving. The SelectionChange event tells you which cell is entered. If you are validating data entered you can use change - the Target argument tells you the cell that triggered the event - so you can just test for the address of target

Workbook Events

Unlike regular macros which are installed in regular modules, Workbook Events are installed in ThisWorkBook the following manner: F11 (Visual Basic Editor), Ctrl+R (VBA Project), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7).

Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Private Sub Workbook_WindowResize(ByVal Wn As Window)

Worksheet Events (continued)

Trapping Ctrl+V for use in Worksheet_Change

The Worksheet_Change event will not pick up changes made by calculation (see Worksheet_Calculate), nor cells changed by pasteing.
For pasteing Bob Ulmas 2001-08-08 suggested.
In your workbook_open event, trap ctrl/v:
Application.Onkey "^v","RunMyPaste"

Invoke another macro on a change event

Automatically invoke a macro after entering data into a particular column.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 4 Then myVBMacro End Sub

Place current date constant in Column A when Column B changes

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 2 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  If IsEmpty(Target.Offset(0, -1)) Then _
    Target.Offset(0, -1) = Format(Date, "yyyy-mm-dd ")
End Sub

Place a count of entries in Column A

The following EVENT subroutine will place a number in column A that is actually based on the ROW number but is intended not to confuse the intended reader with a number that does not match the number of entries made. There are some escapes in the macro to make sure that there is something possibly entered into Column B, and to make sure that changes to the heading row will not affect Column A. (see directions shown in blue to install)
Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'to install -- rightclick on the sheettab of the sheet to ' be used in and choose 'view code'. Paste this Worksheet ' event macro into the module. 'This subroutine will fill in a formula in column A, when a 'Change is made to Column B If Target.Column <> 2 Then Exit Sub If Target.Row = 1 Then Exit Sub If Left(Target.Offset(0, -1), 1) = "~" Then Exit Sub If Left(Target.Offset(0, -1), 1) = "~" Then Exit Sub If Left(Target.Offset(0, -1), 1) = "=Row()-1" Then Exit Sub Target.Offset(0, -1).Formula = "=Row()-1" End Sub

A B C

1 count lastname firstname

2 1 Adams Chris

3 2 McRitchie David

4 3 Wilson Pat

Whenever a change is made in Column B, a number will be entered into column A based on the row number. The calculated number will actually be representing the number of entries allowing a count to be included without having to manually use the fill-handle or having to place a count in ahead of time which could cause lots of unnecessary pages to be printed.

One word of caution: If you change event is going to change the content of a cell that would be affected by the change event you want to turn off events while processing.
Application.EnableEvents = False
...your coding...
Application.EnableEvents = True

Some variations for Change Event

Change cell to UPPERCASE
ActiveCell.Value = UCase(ActiveCell.Value)
'Use upper_case macro to fix preexisting entries.
Change cell to Proper or Title Case
If Target.HasFormula = False Then Target.Value = Application.Proper(Target.Value)
'Use proper_case macro to fix preexisting entries.
Place a running total in Col B when cell in Col A is changed
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Formula = "=SUM($A$2:A" & Target.Row & ")"

Guarantee Negative number

The following will suppress triggering a change event from within the macro. Any positive number will be changed to a negative number. Modified from suggestion by Bernie Deitrick 2000-06-12.
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 3 Then Exit Sub
  If Target.Value <= 0 Then Exit Sub
  Application.EnableEvents = False
  Target.Value = -Abs(Target.Value)
  Application.EnableEvents = True
End Sub

Color Cell in Column A of row when the Selection changes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("A:A").Interior.ColorIndex = 0  'Turn off previous use
   Cells(Target.Row, 1).Interior.ColorIndex = 3
End Sub

Color Row(s) of the currently selected cells

Includes a means of essentially turning the macro on or off. Doesn't really turn off the macro but the test for a value in cell A1 does prevent continued coloring from occurring.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Cells.Interior.colorindex = 0  'Turn off previous use
   If Cells(1, 1) = "." Then Exit Sub
   Target.EntireRow.Interior.colorindex = 38
End Sub

Color a Cell When a Change is Made

This may be useful if you want to know if changes are made such as after publication. In this case we will not check the column or row of the changed cell. Note: Excel has a Highlight Change facility which you might use alone or with the simpler of the two macros below.
Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 8 End Sub
To only effect a change only if within the predefined named range of testb i.e. Range("$A$1:$D$4")
Private Sub Worksheet_SelectionChange(ByVal target As Range) If Intersect(Range("testb"), Range(target(1).Address)) _ Is Nothing Then Exit Sub Range("testb").Interior.ColorIndex = xlNone ActiveCell.Interior.Color = vbCyan End Sub
Here is a version that would include author and revision timestamp in the cell comment.
Private Sub Worksheet_Change(ByVal Target As Range)
  'David McRitchie, 2000-06-16
  '  http://www.mvps.org/dmcritchie/excel/event.htm
  Target.Interior.ColorIndex = 8
  On Error Resume Next
  Dim curComment As String
  curComment = ""
  curComment = Target.Comment.Text
  If curComment <> "" Then curComment = curComment & Chr(10)
  Target.AddComment
  Target.Comment.Text Text:=curComment & _
   ActiveWorkbook.BuiltinDocumentProperties("Author") & _
   Chr(10) & " Rev.  " & Format(Date, "yyyy-mm-dd ") & _
   Format(Time, "hh:mm")
  'comment perhaps should be resized
End Sub
See color.htm for other color choices.
Color Dependent cells with same Color Font as Active Cell -- DClick
A color change is not automatically detected with an Event macro so you will have to trigger it in some manner, below the Event macro is triggered with a double-click (DClick).
After running the macro the dependent cells will be selected but not the cell that was active when you invoked the macro. If you need to include the original active cell in the selection you could lookup "Union Method Example" in VBA Help. You would have to save the address first.
Private Sub Worksheet_BeforeDoubleClick(ByVal _
    Target As Range, Cancel As Boolean)
  Dim FColor As String
  FColor = ActiveCell.Font.colorindex
  ActiveCell.Dependents.Select
  Selection.Font.colorindex = FColor
End Sub

Change Color of Cell depending on first letter (A to G)

B C D E F

18 AR AR AR BR BR

19 B19 C19 D19 G F19

20 B20 G F20

21 B21 C21 D21 E21 F21

To allow clearing of multiple cells changed Target to Target(1), which worked but not sure why. This Test becomes redundant though if the Intersect test is included to reduce scope to only a certain range on the spreadsheet. Color takes effect after hitting, Enter, ArrowKey, TabKey. Change will not occur if mouse moves to another cell instead. Prior to changing target(1).value without a loop to Cell.value a fill-down would use the color generated by the first cell. Revised so that each cell is checked and changed accordingly.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08   rev.  2000-08-14
  '  http://www.mvps.org/dmcritchie/excel/event.htm

Dim vLetter As String
Dim vColor As Integer
   Dim cRange As Range
   Dim cell As Range
   '***************** check range ****
   Set cRange = Intersect(Range("B4:J19"), Range(Target(1).Address))
   If cRange Is Nothing Then Exit Sub
   '**********************************
   For Each cell In Target
      vLetter = UCase(Left(cell.Value & " ", 1))
     'see color.htm and event.htm in same directory as
     ' http://www.mvps.org/dmcritchie/excel/excel.htm
     vColor = 0      'default is no color
     Select Case vLetter
        Case "A"
            vColor = 34
        Case "B"
            vColor = 36
        Case "C"
            vColor = 39
        Case "D"
            vColor = 41
        Case "E"
            vColor = 38
        Case "F"
            vColor = 37
        Case "G"
            vColor = 35
     End Select
     cell.Interior.colorindex = vColor
   Next cell
     'Target.Offset(0, 1).Interior.colorindex = vColor
     ' use Text instead of Interior if you prefer
End Sub
For a variation that colors cell to the right instead use:
Target.Offset(0, 1).Interior.colorindex = vColor
For a variation that uses Array statements see this posted reply by J.E. McGimpsey 2002-02-07.

Using an Event Macro to Change Formulas in a Named Range, or Column

The following is a rather interesting application of the change Event to change the macros in a range. From a solution provided by Bernie Deitrich in the programming group 2000-06-16.
Populate the worksheet as shown in Before.
Select D1:D5, Insert, Name, define, FormulaRange, ok
Install the macro in the worksheet in the manner described at beginning of this page.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then Target.Copy Range("FormulaRange")
End Sub
Now change the the formula in cell D1 and the formulas in the range FormulaRange (D1:d5) change automatically.

Before

A B C D E

1 1 7 a 8 =A1+B1

2 6 2 b 8 =A2+B2

3 5 6 c 11 =A3+B3

4 8 4 d 12 =A4+B4

5 3 9 e 12 =A5+B5

6

7 23 28 51 =SUM(D1:D5)

After installing macro and
changing formula in cell D1.

A B C D E

1 1 7 a 1 =A1

2 6 2 b 6 =A2

3 5 6 c 5 =A3

4 8 4 d 8 =A4

5 3 9 e 3 =A5

6

7 23 28 23 =SUM(D1:D5)

The other macro included by Bernie simulates changing the formula in D1 and then DClick on the Fill Handle in D1 which would replicate formula downard as long as there is data to the right. This subroutine is similar but will replicate down column to the last cell with content or no content depending on if cell D2 has content or not, you presumably would have had formulas in column D, but not necessarily in the column to the left of Column D.
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$D$1" Then Target.Copy Range(Target, _
    Target.End(xlDown))
End Sub
DateTimeStamp in Column A, on first entry in any other column on row
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not IsEmpty(Cells(Target.Row, 1)) Then Exit Sub
   If Not IsEmpty(Target.Value) Then Cells(Target.Row, 1) = Now
End Sub
To update column A, when anything is entered/changed in same row use:
Private Sub Worksheet_Change(ByVal Target As Range)
   If target.row = 1 then exit sub   'don't touch if 1st row
	 If target.colum = 1 then exit sub
   If Not IsEmpty(Cells(Target.Row, 1)) Then Exit Sub
   Cells(Target.Row, 1) = Now
End Sub	 
	 
Compare two cells and run a macro if unmatched
This example will compare a changed cell in column A or B and compare the changed value to the other column. If the cells are not equal a regular macro will be invoked.
   Option Explicit
   Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column > 2 Then Exit Sub  'A or B okay
      If Cells(Target.Row, 1) <> Cells(Target.Row, 2) Then
        MsgBox Target.Address & " value does not match paired column"
        Call stupid_macro(Target.Address & "value in column A is " _
            & cells(Target.row,1)  _
            & ",  value in Column B is " & cells(Target.row,2) )
      End If
   End Sub
Your other macro is installed in your project library might look like this
   Option Explicit
   Sub stupid_macro(Optional str1 As String)
      MsgBox "hello world, you gave me " & str1
   End Sub
Notes on Worksheet_Change

Target range, the changed range. Can be more than one cell.
This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.
Deleting cells doesn't trigger this event. (true in XL97, but will trigger change event in XL2000).
Example: (from a posting by Efstratios Malasiotis 2000-06-09 in programming.) You can test the following by copying using the fillhandle, selecting a range and then using Del key, or Clear under edit. Deleting cells also triggered the change event. Note use of IIF function which simulates Worksheet IF function.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  MsgBox Target.Address & vbCr & IIf(Target.Cells.Count > 1, _
      "Multiple", "Single")
End Sub
Reported in same thread XL97 does not trigger change event when deleting cells as written in HELP, but event is triggered in XL2000. Another observation of difference in XL97 and XL2000, reported in same thread. Select a group of cells, A1:A7, hit enter down to A5, no change reported. Change cell A5 in the group, XL97 will report the full range and multiple, XL2000 will report cell A5 and single.
Problems with worksheet change
Please read Q172832 -- XL97: Worksheet Change Event Macro Fails to Run, pasting a value into a cell may not trigger change
Worksheet_BeforeRightClick
The following EVENT subroutine will invoke the macro named in the cell upon a right click. Since the macro is for a specific worksheet, we can be very specific as to which column this will apply to.&nsp; As in the previous example there are escapes if not in the correct area.
Private Sub Worksheet_BeforeRightClick(ByVal Target _ As Excel.Range, Cancel As Boolean) 'to install -- rightclick on the sheettab of the corresponding ' sheet and choose 'view code'. Paste the following procedure ' in the module. 'RightClick on cell in column C will invoke macro in that cell If Target.Column <> 3 Then Exit Sub If Target.Row = 1 Then Exit Sub On Error Resume Next 'MsgBox ActiveCell.Value Application.Run ActiveCell.Value Application.Run "testng2k.xls!" & ActiveCell.Value Cancel = True 'prevents normal RClick menu from appearing End Sub
Some variations for Worksheet_BeforeRightClick Event
Toggle value between "" or "x"
If ActiveCell.Value = "x" Then
      ActiveCell.Value = ""
  Else
      ActiveCell.Value = "x"
  End If
Multiple cells changed

Change Event
Occurs when cells on the worksheet are changed by the user or by an external link.
Syntax
Private Sub Worksheet_Change(ByVal Target As Range)
Target The changed range. Can be more than one cell.
Remarks
This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.
Deleting cells doesn't trigger this event.<

Worksheet_BeforeDoubleClick
Double-Click on cell to navigate to the corresponding cell (same cell address) on the other sheet.
Specifically it was designed to select a cell and then read detail or comments in another sheet.
Note we can make use of the Target.Address from the sheet that was double-clicked from even though we have activated the second sheet at that point.
Can be used with Grouped selected sheets watch out what you update if you group them. (based on 2000-03-02 programming Wilson/McRitchie)
Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
   Worksheets("Comments").Activate
   ActiveSheet.Range(Target.Address).Activate
End Sub
Here is an Event macro just double-click below a bunch of contiguous cells in a column similar to the feature of the Sum toolbar icon that can do this. No icon needed here. I don't know how to duplicate the selections you would see with Sum icon, but the following will show the selection so that you will know which cells were involved.
Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) 'David McRitchie, misc, 2001-07-02 '-- Find top cell in continguous range Range(Target.Offset(-1, 0).End(xlUp), Target).Select '-- leave selection of cells showing for visual verification. Target.Formula = "=SUBTOTAL(9," _ & Selection(1).Address(0, 0)& ":" _ & Selection(Selection.Count - 1).Address(0, 0) & ")" 'make the double-clicked cell the active cell for the range Target.Activate End Sub
Worksheet_SelectionChange
Worksheet_SelectionChange to prevent entry past a column
The purpose of this macro is that the cursor hits a brick wall at column D and returns to the beginning (Col A) of the next row. Use the TAB key to go to next cell. No cell protection or validation is required for this macro.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Column > 3 Then
      ActiveCell.Offset(1, 1 - Target.Column).Select
   End If
End Sub
Locked cells to prevent entry and to prevent selection with the tab key
Another way is to unlock cells that can be entered into and then protect the worksheet. Users cannot enter or select locked cells on protected sheets.
Format, Cells, protection, locked/unlocked
Tools, Protection, Protect Sheet
The default is that all cells are locked, but that has no effect until Sheet protection is turned on. So you select ALL (ctrl+A) or select columns to make changes to larger areas, generally starting from all cells either locked or unlocked and change them accordingly.
You cannot tab to a protected cell. Use of the above Event macro in combination with a protected cell in Column A might look like you tabbed to A, but it was the macro that put you to A not the Tab.
Worksheet_SelectionChange to prevent entry in certain columns
Entry into certain columns is prevented until there is an entry in Column B. (alternative is Cell validation)

A B C D E F G H I J K L M N O P Q R S

1 NO 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19

2 aaa d b b

3 d d

4 dd

5

6

When attempting to create the HTML table above, I discovered that the Selection within my XL2HTMLx macro was interfering with it's invocation, so I had to put in a bypass of "NO" in cell A1.
The criteria is that nothing can be coded in a cell unless the cell in column 1 has been coded; and nothing can be coded in columns 9, 11, 13, 15, 17 until column 2 (col B) has been entered. I prefer a BEEP to the information msgbox. A .wav file can be substituted for BEEP if not available. (Coded for Mike Walsh 2000-09-01 thru 09-04 in worksheet.functions).
The example above was my test data. Selection of any cell on rows 2 and 3 can be entered into without impairment. Selection of G4, I4, K4, M4, O4, or Q4 will result in repositioning of cursor to B4. Selection of any cell in row 5 will result in repositioning of cursor to A5 because column A must be filled in regardless.
The "NO" in A1 would prevent any of the above from being checked for.
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Cells(1, 1) = "NO" Then Exit Sub
'to install -- rightclick on the sheettab of the corresponding
' sheet and choose 'view code'.  Paste the following procedure
' in the module.
  If Target.Row = 1 Then Exit Sub 'no description

  'to require something in column 1 include this
  If Trim(Cells(Target.Row, 1)) = "" Then
    Cells(Target.Row, 1).Select
    Exit Sub
  End If

  'no further interest if the is something in Column 2
  If Trim(Cells(Target.Row, 2)) <> "" Then Exit Sub

  '-If there is nothing in column 2,
  ' disallow entry in cols 7, 9, 11, 13, 15, 17
  ' and then select column 2
  If Target.Column Mod 2 = 0 Then Exit Sub  'even columns
  If Target.Column < 7 Or Target.Column > 17 Then Exit Sub

  'beep     'Beep  ..   MAY NOT BE ON YOUR SYSTEM
  Drumroll_wav

  MsgBox "Please enter something in cell B" & Target.Row() & _
     Chr(10) & "before " & "entering anything into cell " & _
     ActiveCell.Address(0, 0), vbExclamation
  Cells(Target.Row, 2).Select
End Sub
In order to get the Drumroll_wav to work had to set up a macro in a standard code module. (code at code/beeps.txt
Updating in a selected range, including within a Named Range
Howard Kittle (2002-01-23) came with this interesting method of applying multiple ranges within a named label to advance to the next cell in the range with the TAB or Enter key. A single range has long been a trick to enter data different than your usage. But this multiple range is very interesting.&nbs; Hold the Ctrl key down while selecting A2, C2, E2, G2, I2, H3, F3, D3, B3, A4, C4, E4, G4, A2 (back to original) than place a name "whatever" into the name box. Forward and backward shows off what can be done as opposed to more convential practical group of selections.
Making the Cursor More Visible
Here are some alternatives to making the Cursor more Visible.
To left of toolbar and above the A1 cell is the address of the cursor. but someone will probably still insist on a macro to put the selected range in A1, if you only want the activecell use ActiveCell instead of Selection..
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("A1").Value = Selection.Address(0, 0)
End Sub
Change the mouse pointer arrow to another, under START--> settings -> control panel --> mouse --> Pointers --> (Normal should be large white arrow)
if not [Use Reset] is not grayed out they have been changed.
There is an option on the wheel mouse at least that if you hit Ctrl you get large target to smaller target circles, see the Visibility Tab, in fact there is an another option there that hides the cursor while typing. (make sure the invisible one is not selected). This ability is lost after applying a mouse "fix".
Otherwise as last resort, use an selection change event macro.
Highlght Active Cell on Chip Pearson's Macro page.
And if that is not enough, highlight row, column, and cell. This uses target but you might prefer activecell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'to install -- rightclick on the sheettab of the corresponding
' sheet and choose 'view code'.   Paste the following procedure
' in the module.  Choose more suitable colors:
'   -- http://www.mvps.org/dmcritchie/excel/colors.htm
  Cells.Interior.color = xlAutomatic
  Rows(Target.Row).Interior.colorindex = 3
  Columns(Target.Column).Interior.colorindex = 4
  Target.Interior.colorindex = 8
End Sub
Worksheet_Activate Event
The following macro will find the first occurrence of the current date in Column B when invoked. To automatically invoke this macro when the sheet is opened it will be invoked by the Worksheet_Activate Event. Posted 2001-07-27 D.McRitchie, Programming. Tested only for US dates, if there is a problem with UK dates check T.Ogilvy's reply in same thread. Related information can be found in BuildTOC for more macro that reposition screen view. Also see my Date & Time page.
Option Explicit
Sub FindTodayIn_Col_B()
    Columns("B:B").Select
    Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    Application.Goto Reference:=Cells(ActiveCell.Row, 1), Scroll:=True
    ActiveCell.Offset(0, 1).Activate    'make date the selected cell
End Sub.
Invoke the above from a Sheet Event.
Option Explicit Private Sub Worksheet_Activate() Call FindTodayIn_Col_B End Sub
Workbook Events
Workbook events are very similar to Worksheet Events.
The following is a worksheet event, it applies to only one worksheet and is installed with the worksheet.
   
Private Sub WorkSheet_Activate()
    MsgBox "You have activated sheet: " & ActiveSheet.Name
End Sub
The following is a workbook event, it is activated by any sheet being activated. You can restrict it's usage by checking the worksheet name that was activated. Workbook events are installed in ThisWorkbook.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   if sh.name = "My First Sheet" then
      ..more code..
   end if
end sub

additional code you might need:
   Application.EnableEvents = False
   {switch sheets}
   Application.EnableEvents = False
Workbook Changes
Workbook Change Event
The following would change any cell changed in the entire workbook to capitals including most formulas. This gets placed behind the workbook. (Alt+F11) Exceptions would be values from other sheets and use of something like PROPER worksheet function. Jake Marx 2000-05-03 in excel misc group.
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
 ByVal Target As Range)
    Target.Formula = UCase$(Target.Formula)
End Sub
Fixup the Existing Workbook Entries
Run the following from a regular module to convert preexisting entries to uppercase now that you are changing all new entries in the workbook. Jake Marx 2000-05-03 in excel misc group.
Sub MakeAllUCase()
    Dim ws As Worksheet
    Dim rng As Range

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    For Each ws In ActiveWorkbook.Worksheets
        For Each rng In ws.UsedRange
            rng.Formula = UCase$(rng.Formula)
        Next rng
    Next ws

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
Related Information

An change event example has been added to Insert a Row using a Macro to maintain formulas although the primary focus is on inserting rows with formulas in place instead of using a change event to include the macros. The common part is the use of OFFSET in the examples. Describes a Worksheet Change event to set up formulas, and formatting based on entry of date on a check balance sheet.
See related area in Highlight Change for links to Calculate event.
BuildTOC, Using an Event to suppress generation of hyperlinks. Also see MS KB q233073 it is an Event macro that will be invoked
Adding / Changing Cell Comments, places user identification into cell comments when a change is made.
Fun Stuff, Playing Wave Files, triggered by worksheet selection change. Example set up to place a list of all .wav files on your hard drive and to play them automatically or individually.
pathname, Workbook_BeforePrint event.
proper, Worksheet change to convert a column to capitals such as US zip state code when entered.
Slow Response, be sure to limit Event macro to the cells it should be checking.
Summarizing Data, Conditional Formatting, reference back to here as a substitute for Conditional Formatting not limited to 3 conditions.
Collections, An Event macro to help with displaying all of the right click menus in support of an example from Jim Rech.

For more information about Change Events see:

Event Procedures In Microsoft� Excel97� Chip Pearson, including new topic "Order of Events"
Application Level Events, Chip Pearson
VBE HELP (Alt+F11 then F1) because help for VBA is with the Visual Basic Editor, not with Excel.
Also search Google Newsgroup Archives, you would want to search for both "Sub" and for "Worksheet_Change", for working examples. Since they will be together you could search for them as a phrase actually using the double quotes "Sub Worksheet_Change", more information on searching Google Usenet Archives can be found on my Newsgroups page.

For those with money, or a department budget, or who just hang out at bookstores w/o buying anything

Excel 2000 Power Programming With VBA John Walkenbach; see Chapter 18, "Understanding Excel's Events", 869 pages + CDROM; ISBN 0764532634; US$49.99;

Microsoft Knowledge Data Base (MS KB)

See additional information on Newsgroup page and in the onsite/offsite index page.

This page was introduced on April 19, 2000.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]
Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	NO	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19
2	aaa	d								b	b
3	d	d
4	dd
5
6

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	NO	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19
2	aaa	d								b	b
3	d	d
4	dd
5
6

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	NO	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19
2	aaa	d								b	b
3	d	d
4	dd
5
6