Event Macros, Worksheet Events and Workbook Events

Note before continuing

A  Worksheet_Change   event:  triggers when you change a cell (or range of cells) value manually or in a macro -- it will not be triggered from a change showing up in a formula or from a change of format.  Your change macro might be used to automatically provide a date to another cell, , see autodate. 

Some words 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.  Loss of the ability to undo changes (Ctrl+Z) like using any other macro, is an important consideration.

Related Features:  If you are thinking of using an Event macro to restrict input, you might want to check Data --> Validation features.  Conditional Formatting is faster for changing colors, but is limited to 3 conditions (color choices) after which you need an event macro such as one using the case statement.

Security set to Medium:  If you have never run macros before, make sure that your security is set to medium to allow you to run macros, by making a choice when you open each Excel workbook.  (Tools, Options, Macros, Security, Medium)  The default in later versions of Excel is High which prevents use of macros.

Excel 2007 and 2010  Event macros must be in the workbook you use them in -- this has not changed, but you must rename the workbook from .xls to .xlsm (for macros).  Also make the following changes so that you can activate macros.  File Menu (Office Button) → "Excel Options" → Trust Center → Settings → (1) Macro settings → Disable all macros with notification, and Turst access to the VBA project object model. (2) Message bar → "Show the Message Bar in all application when content has been blocked".  This will enable you to reenable content for each session.

to use:  Above the address bar you will see "Security warning macros have been disabled.", you will have to click on "Options" to Enable the content in order to run macros including your own.  In the VBA View (Alt+F11) use: F7 - view code, Ctrl+F to view the Project Explorer, and Ctrl+G to view the Intermediate Window (for one line tests).

Worksheet Events   (#blueboxWS)

Unlike standard macros which are installed in standard modules, Worksheet Events are installed with the worksheet by right-clicking on the sheettab, choose 'view code', and then paste in your macro.   [Back]

These are the Worksheet Events available in Excel, as found in the dropdown on within “View Code” after Right-Click on a sheet tab.
 
Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)     -- (additional examples)
    Cancel = True   'turn off Edit mode when using “Edit directly in a cell”
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True   'turn off Edit mode when using “Edit directly in a cell”
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 standard 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 (MSDN Office 97 archive)
   Chapter 4 -- Microsoft Excel Objects
    http://www.microsoft.com/officedev/articles/Opg/004/004.htm (MSDN Office 97 archive)
The above had been missing but were found by a reader -- thanks for updates.

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

There is separate topic for Checkmarks (P) Using the "Wingdings 2" font (#ticks) at then end of the Worksheet Events providing a choice of selection, double-click, or right-click.

Continue at Worksheet Events (continued, #worksheet2)

Workbook Events   (#blueboxWB)

Unlike standard macros which are installed in standard modules, Workbook Events are installed in ThisWorkBook in the following manner:  Alt+F11 (Visual Basic Editor), Get into your project library (name of your workbook) with Ctrl+R (View, Project Explorer), 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).  You can also add the Intermediate window (Ctrl+G) for single line testing. You will find those options in the VBA View Menu. Shortcut from Excel:  RightClick on the Excel logo to left of file menu, then use “View Code”.   [Back]

Private Sub Workbook_AddinUninstall()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Workbook_Deactivate()
Private Sub Workbook_NewSheet(ByVal Sh As Object)
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) Continue at Workbook Events (continued)

Worksheet Events (continued, #worksheet2)

Worksheet_Calculate Event   (#calculate)

This is when an Conditional Format would have a big advantage, except that C.F. is limited to 3 conditions, but the C.F. would eliminate the need to code both worksheet_Calculate and Worksheet_Change as it does not care where or how the values changed.

Excel 2002 includes format as a change Event so possibly this should change the colorindex at the end and turn Event off/on around it.  But I don’t have Excel 2002.

Private Sub Worksheet_Calculate()
   '-- formula P2:  =MIN(J2,K2,L2,M2,N2,O2)
   Dim cell As Range
   Dim vColor As Long
   For Each cell In Intersect(Columns("P"), ActiveSheet.UsedRange)
       Select Case cell.Value
        Case ""
          vColor = -4142
        Case Cells(cell.Row, 10)   '-- J
          vColor = 40
        Case Cells(cell.Row, 11)   '-- K
          vColor = 36
        Case Cells(cell.Row, 12)   '-- L
          vColor = 45
        Case Cells(cell.Row, 13)   '-- M
          vColor = 39
        Case Cells(cell.Row, 14)   '-- N
          vColor = 4
        Case Cells(cell.Row, 15)   '-- O
          vColor = 8
        Case Else
          vColor = -4142
       End Select
      'Excel 2002 has format as a change event, don't want to trigger it
      Application.EnableEvents = False   'should be part of Change macro
      cell.Interior.ColorIndex = vColor
      Application.EnableEvents = True   'should be part of Change macro
   Next cell
End Sub
'-- ColorIndex  http://www.mvps.org/dmcritchie/excel/colors.htm
'-- Event       http://www.mvps.org/dmcritchie/excel/event.htm

'-- ColorIndex  http://www.mvps.org/dmcritchie/excel/colors.htm

Essentially you are running a macro for the entire sheet that is t triggered by a Calculate Event.

You could for instance have your actual macro as a macro in a standard module and then call it from a Calculate Event, which would allow you use the same macro in selected worksheets in selected workbooks.

The advantage of having only a worksheet event macro is that you delete the sheet you don’t have old code cluttering your libraries

Sub MinSubFor_xyz()
   'code as shown above...
End Sub

Private Sub Worksheet_Calculate()
   MinSubFor_xyz
End Sub 

Worksheet_Change Event   (#change)

The Change Event macro is the Event macro, I use the most, and is one of the most efficient.  There are several Change Event macros on this page including one specifically using the Case Statement, also see the ReEnterForChangeMacro macro to fixup pre-existing entries to your Change Event macro or after making changes to your Change Event Macro.

If you don't want to name a column by it's number as in IF target.column >= 23 AND target.column <= 43 then you can use If Not Intersect(Range("W:AP"), target.Cells) Is Nothing Then

The following is more or less the equivalent of cell B2 containing the formula =IF(ISERR(A2 * 2),"",A2 * 2)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Then Exit Sub

  On Error goto ErrHandler
  Application.EnableEvents = False
  If Target.Column = 1 Then
     If IsNumeric(Target.Value) Then
       Target.Offset(0, 1).Value = Target.Value * 2
     Else
        Target.Offset(0, 1).Clear
     End If
  End If
ErrHandler:
  Application.EnableEvents = True
End Sub

Clear row to end on change (#clear_rest)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.row = 1 Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  On Error GoTo ErrHandler
  Application.EnableEvents = False
  If Target.Column > 1 Then
     '-- create a range with two points Range(point1,point2)
     Range(Target.Offset(0, 1), Target.Offset(0, _
        Cells.SpecialCells(xlLastCell).Column _
         - Target.Column)).Select 'change to .Clear & remove blue coding
     If Target.Offset(0, 1).Interior.ColorIndex = 6 Then
        Selection.Interior.ColorIndex = 20
        Selection.Interior.Pattern = xlSolid
     Else
        Selection.Interior.ColorIndex = 6
        Selection.Interior.Pattern = xlSolid
     End If
    ' Selection.Clear
  End If
ErrHandler:
  Application.EnableEvents = True
End Sub

Trapping Ctrl+V for use in Worksheet_Change (#ctrl_v)

The Worksheet_Change event will not pick up changes made by calculation (see Worksheet_Calculate), nor cells changed by pasting.

For pasting Bob Ulmas 2001-08-08 suggested, and you would have to do your own Selection.PasteSpecial after intercepting the Cnrl+V.

In your workbook_open event, trap ctrl/v:
Application.Onkey "^v","RunMyPaste"

Invoke another macro on a change event (#invokemacro)

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 (#autodate)

You can use NOW for date + time, or you can use DATE or you can use TIME. 

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(1)) then Exit Sub 
  If IsEmpty(Target.Offset(0, -1)) Then  
    Target.Offset(0, -1) = Date
    Target.offset(0, -1).numberformat = "yyyy-mm-dd"
  End If
End Sub

Also see DateTimeStamp in Column A, on first entry in any other column on row (#datetimestamp) and next topic.

or Place current time constant in Column A when Column B changes (#autotime)

Private Sub Worksheet_Change(ByVal Target As Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'.   Paste this Worksheet
' event macro into the module.
  If Target.Column <> 2 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  If IsEmpty(Target(1)) Then Exit Sub
  If IsEmpty(Target.Offset(0, -1)) Then
    Target.Offset(0, -1) = TimeSerial(Hour(Time), Minute(Time), 0)
    Target.Offset(0, -1).NumberFormat = "hh:mm"
  End If
End Sub

DateStamp in Column B on entry into Column A

The date into column A is really more for a checkbook, a more common request is for the date to be placed into column B, so will include this also as a specific example.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 1 Then Exit Sub
  If IsEmpty(Target(1)) Then Exit Sub  '-- also rejects if entire row cleared
  If Target.row = 1 Then Exit Sub
  If IsEmpty(Target.Offset(0, 1)) Then
    Target.Offset(0, 1) = Date
    Target.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
  End If
End Sub

or When a specific cell changes, place timestamp in cell -- A2 changes when A1 changes (#celltimestamp)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target = Cells(1, 1) Then Cells(2, 1) = Now
End Sub     'alternatives:  NOW()  or date   or   Time

If you simply want to enter a timestamp into the double-clicked cell you can use the following macro.  Time only goes down to minutes, so if you want to see seconds you will have to use NOW which includes both date and time.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
  Cancel = True
  Target.Value = Now    'only down to seconds
  Target.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub

If you only wanted Time then use:

    Target.Value = Now  Mod 1
    Target.NumberFomat = "hh:mm:ss"

It would be more efficient to format the entire column beforehand rather than formatting individually within the Event macro.  Keep in mind that such macros are written specifically for the worksheet they are in and you can check the column to see if that is a column that you actually want to update.

Adjust row height automatically(#autofit)

Double-clicking on the line between the row numbers will adjust the row height of the row above.  You can do this also from Format, row, autofit.  The following macro will do this automatically, whenever you change a value.  Formulas will not invoke the macro -- you can still take manual steps to adjust the row height.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False   'should be part of Change macro
    Cells.EntireRow.AutoFit
    Application.EnableEvents = True   'should be part of Change macro
End Sub

If you have merged cell then AutoFit will not work and you would want to use a macro written by Jim Rech AutoFitMergedCellRowHeight

Place a count of entries in Column A (#rowcount)

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 -- right-click 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.

Use caution with Event Macros::  If your worksheet_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 to prevent inadvertent reprocessing that would be caused by processing the event.  Warning:  If you do not have error recovery for this and the macros between, you will not be able to run Event macros until you reenable events.

    Application.EnableEvents = False
    ...your coding...
    Application.EnableEvents = True

Some variations for Change Event (#change_v)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column > 8 Then Exit Sub
    On Error goto ErrHandler
    Application.EnableEvents = False
    Target.Formula = UCase(Target.Formula)
  ErrHandler:
    Application.EnableEvents = True
End Sub

Change cell to UPPERCASE (#uppercase)

Target.Formula = UCase(Target.Formula)
'Use upper_case macro to fix preexisting entries. 
'Note use of .Formula instead of .Value as a means to protect formulas.

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 & ")"

Change cell to Proper or Title Case (#propercase)

If Target.HasFormula = False Then Target.Value = StrConv(target.value,vbProperCase)
'Use proper_case macro to fix preexisting entries.

Use the VBA StrConv function instead of application.proper.

Invoke Proper_Case Macro from a Change Event Macro

If you want to be a bit fancier so that   david mcritchie   comes out as   David McRitchie  

To make this happen for the changed cell you can have your Change Event macro call the Proper_Case macro.  Because the Target cell will not be the selected cell after entry an optional parameter was added to the Proper_case macro which is the address of the range to be changed.  The following code can also be seen at Invoking a Change Event macro to change to Proper Case.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Row  = 1 then Exit Sub 'don't override headings in row 1 
  If Target.Column <> 4 Then Exit Sub  'only allow changes to Col D 
  Application.EnableEvents = False
  Application.Run "personal.xls!Proper_Case_inner", Target.Address
  Application.EnableEvents = True
End Sub

Without the optional parameter on the Proper_Case macro you have to add extra code which works only if you use the defaut move down after entry.  Original Proper_Case macro was renamed to proper_case_inner and Proper_Case macro invokes the inner one -- changed so that Proper_Case will still show up on the macro list (Ctrl+F8).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 If Target.Column <> 4 Then Exit Sub
 Application.EnableEvents = False
 Target.Activate
 Application.Run "personal.xls!Proper_Case"
 Target.Offset(1, 0).Activate
 Application.EnableEvents = True
End Sub

Guarantee Negative number (#neg)

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

Using a cell as an Accumulator -- link (#accumulator)

A frequent request is to accumulate at total of previously entered values in another cell.  Since there is no way to validate or track down the history of what was entered it would generally be discouraged, but it can be done with a Change Event see John McGimpsey's Using a Cell as an Accumulator.

Print Area based on lastcell width and last use in Column A (#printarea)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lLastRow As Long, iLastCol As Long  'Nick Hodge/D.McR, 2004-09-25  printing
  lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  iLastCol = Cells.SpecialCells(xlLastCell).Column
  '-- Cells.Interior.ColorIndex = xlColorIndexAutomatic
  ActiveSheet.PageSetup.PrintArea = _
  Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Address
  '-- Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Interior.ColorIndex = 37
End Sub

Color Cell in Column A of row when the Selection changes (#rowcolor)

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 (#colorrows)

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 (#chg010)

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 (#revision).

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 colors.htm for other color choices.

Color Dependent cells with same Color Font as Active Cell -- Double-Click (#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
  Cancel = True   'Get out of edit mode 
  FColor = ActiveCell.Font.colorindex
  ActiveCell.Dependents.Select
  Selection.Font.colorindex = FColor
End Sub

Additional examples of double-click not related to color are below topic Worksheet_BeforeDoubleClick (#ws_bdc)

Change Color of Cell depending on first letter (A to G),  case statement (#case)

The Worksheet Change Event macro picks up manual changes.  When Conditional Formatting needs more than three Formatting groups per cell the next step is to use a Change Event macro.  The normal advantage of C.F. is that it makes no difference how the value changed, manually, or with formulas.  Pasting Formats:  Pasting can wipe out C.F. in a cell). 
 

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 Long
   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
   '**********************************

Example of Case Statement in a Change Event macro   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   Excel ColorIndex values 1 53 52 51 49 11 55 56 9 46 12 10 14 5 47 16 3 45 43 50 42 41 13 48 7 44 6 4 8 33 54 15 38 40 36 35 34 37 39 2 (see colors page) 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   For Each cell In Target
      vLetter = UCase(Left(cell.Value & " ", 1))
     'see colors.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
     Application.EnableEvents = False   'should be part of Change macro
     cell.Interior.ColorIndex = vColor
     Application.EnableEvents = True   'should be part of Change macro
   Next cell
     'Target.Offset(0, 1).Interior.colorindex = vColor
     ' use Text instead of Interior if you prefer
End Sub

Similar Example but change the interior color of entire row (#case_row)

The following example will color the entire row based on a change to Column F (column 6).  VBA is case sensitive so the testing will be done with lowercase.  Cells in Target range are checked individually so that use of the fill handle will be effective.  If you started with this topic you may have missed the narrative in the previous topic.

Example for #case_row
Changes interior color for the entire row

	A	B	C	D	E	F	G
1	A1	B1	C1	D1	E1	F1	G1
2	A2	B2	C2	D2	E2	abc	G2
3	A3	B3	C3	D3	E3	Yes	G3
4	A4	B4	C4	D4	E4	NO	G4
5	A5	B5	C5	D5	E5	yes	G5

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming,  Case -- Entire Row
  '  http://www.mvps.org/dmcritchie/excel/event.htm#case
  If Target.Column <> 6 Then Exit Sub   'Column F is column 6
  If Target.Row = 1 Then Exit Sub
       Application.EnableEvents = False   'should be part of Change macro
  Select Case LCase(Target.Value)
     Case "yes"
        Target.EntireRow.Interior.ColorIndex = 34
     Case "no"
        Target.EntireRow.Interior.ColorIndex = 36
     Case Else
        Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
  End Select
  Application.EnableEvents = True   'should be part of Change macro
End Sub

Comments on on the above two Change Event macros with Case Statements (#case_comments)

See ReEnterForChangeMacro macro below if you change your macro, or need to run on existing entries.

When using an interior color, gridlines are wiped out so you might want to create borders for all cells:  Format, Cells, Borders, choose: line type/thickness, and color, choose: borders (inside and outside borders).

Excel 2002 probably treats formatting changes as a change event so have turned off EnableEvents.

If you just want to check a column then instead of checking range use the faster
  IF target.column <> 1 then exit 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.

A non Event macro differentiating text values, numbers, and empty cells in addition to ranges of numbers.  Involves Interect, Union, and SPecialCells.

You may want to have a non-event version of a macro to fixup your data initially and perhaps later should the Event macro fail for any reason, including people using paste to change values.

Normally you would intercept an error message directly below where the error occured and continue depending on the actual error.  A typical example of using Select Case for error messages by Alan Barasch, as shown for when any error will cause termination but you want more information to be shown.  More information on error handling see VBE help topic – “Error Object, Errors Collection, and Description, Number, Source, HelpFile, and HelpContext Properties Example”.  Chip Pearson has a page Error Handling In VBA

For those that would rather use an addin that extends the limit of 3 Conditional Formatting conditions per cell you take a look at CFPlus - Extended Conditional Formatter, Bob Phillips

To Fix colors on Pre-Existing Data (#ReEnterForChangeMacro)

Your Change Event macro will color cells when changed.  This macro will effect a change for each cell in the selection, which is a lot easier than hitting F2 then Enter for each cell to force changes.  (additional ReEnter macros). 

You must select a range to at least encompass the range of your change macro, you can probably use Ctrl+A (select all cells), and you might wish to use clear out all interior color as well before running the following macro.  The reason selection.interior.colorindex = xlnone is not included is because the area in the change macro may differ.  Can even be used to color formulas based on Change event macro. 

Sub ReEnterForChangeMacro()
    'D.McRitchie,  programming,  2004-05-15,  event.htm (join.htm)
    '-- Your change event macro will recolor each cell in selection
    Dim CurrCell As Range
    On Error Resume Next  'in case nothing in selection
    For Each CurrCell In Selection.SpecialCells(xlConstants)
        CurrCell.Formula = Trim(CurrCell.Formula)
    Next
    For Each CurrCell In Selection.SpecialCells(xlFormulas)
        CurrCell.Formula = Trim(CurrCell.Formula)
    Next
End Sub

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 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 Double-Click on the Fill Handle in D1 which would replicate formula downward 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   (#datetimestamp)

If you just want the date use Date instead of Now, also see Place current date constant in Column A when Column B changes (#autodate).  Whether you use Now, Date, or Time they all use the same amount of space to store data as time is a fraction of a day.  So if you want both date and time as timestamp use NOW in a single cell, it will make calculations simpler. -- see my Date & Time page.  Do customize the macro to the specific worksheet as it only applies to the worksheet you install it into. 

The following is the simplest, will update an empty Column A on entry of another cell on the same row.  [Install]

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not IsEmpty(Cells(Target.Row, "A")) Then Exit Sub
   If Not IsEmpty(Target.Value) Then Cells(Target.Row, "A") = Date 'or use NOW
End Sub

Will not update first row, but will update Column A on entry of another cell on the same row.  [Install]

Private Sub Worksheet_Change(ByVal Target As Range)
   If target.row = 1 then exit sub   'don't touch if 1st row
   If target.column = 1 then exit sub 'don't touch with macro if target is column A
   If IsEmpty(target) then exit sub  'allow deletion of cell content in entire row 
   If Not IsEmpty(Cells(Target.Row, 1)) Then Exit Sub 'ignore if already has a value
   Cells(Target.Row, 1) = Now   '--Column should be preformatted
End Sub

Avoid placing cell formatting into an Event Macro as individual cell formatting increases workbooksize, instead format the entire columm.  Also see Author/Datestamp into Cell Comment,  autodate,  autotime.  Use your browser back button (Alt+LtArrow) to return here.

Compare two cells and run a macro if unmatched (#compare2cells)

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 standard 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).
• Although not mentioned in HELP, Pasting will not trigger the Event Change macro.

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

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.  [This statement is false] Deleting cells doesn’t trigger this event.  (true in XL97, but will trigger change event in XL2000), contrary to the HELP in Excel 2000 related above. Although not mentioned in HELP, Pasting will not trigger the Event Change macro. For pasting Bob Ulmas 2001-08-08 suggested, and you would have to do your own Selection.PasteSpecial after intercepting the Cnrl+V. In your workbook_open event, trap ctrl/v: Application.Onkey "^v","RunMyPaste"

Worksheet_BeforeRightClick   (#ws_brc)

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.  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 -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'.  Paste the following procedure
' in the module.
'Right-Click 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 Right-Click menu from appearing
End Sub

Cancel = True   suppresses the right-click (context) menu that would appear after you right-click on a cell, by indicating that the edit is finished -- therefore the context menu will not be brought up.  Cancel = True is also used to terminate Edit when a double-click is invoked.

Some variations for Worksheet_BeforeRightClick Event

Toggle value between "" or "x"

If ActiveCell.Value = "x" Then
      ActiveCell.Value = ""
  Else
      ActiveCell.Value = "x"
  End If

Worksheet_BeforeDoubleClick (#ws_bdc)

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)
   Cancel = True   'Get out of edit mode 
   Worksheets("Comments").Activate
   ActiveSheet.Range(Target.Address).Activate
End Sub

The following will copy formulas down from the last row, and increment the value in Column A of the last row by 7 days for the new row, then place the cursor in the first empty cell of the new row.  (More information on inserting rows and maintaining formulas.)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
  Dim lastrow As Long   'D.McRitchie,  2004-06-10, newusers
  lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
  Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents
  Cells(lastrow + 1, 1) = Cells(lastrow, 1) + 7
  Rows(lastrow + 1).SpecialCells(xlCellTypeBlanks).Item(1).Activate
End Sub

Example to show row address of cell double-clicked, and the content of the cell two cells to the right.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  If Target.Column <> 1 Then Exit Sub
  MsgBox "You could run a macro here " & Chr(10) & _
     "you poked cell " & Target.Address(0, 0) & Chr(10) & _
     "in Row " & target.row & ", " Column " & target.column & _ 
     "The cell two columns to the right displays:  " & _
     Target.Offset(0, 2).Text
End Sub

Hyperlink in first cell on row, used for all on row with double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   On Error Resume Next
   ActiveWorkbook.FollowHyperlink Address:=Cells(Target.Row, 1).Hyperlinks(1).Address, _
      NewWindow:=False, AddHistory:=True
   If Err.Number <> 0 And Err.Number <> 9 Then
      MsgBox Err.Number & "  " & Err.Description
   End If
End Sub

Hide/Unhide Columns toggling with double-click (#hidecols)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
        Target As Range, Cancel As Boolean)
'D.McRitchie, misc 2007-05-11
'to install -- right-click on the sheet tab of the sheet to
' be used in and choose 'view code'.   Paste this Worksheet
' event macro into the module.      2007-05-11 misc
'http://www.mvps.org/dmcritchie/excel/event.htm
  Cancel = True   'get out of entry mode
  If Range("A1").Interior.ColorIndex < 0 Then
      Range("B:B,D:E,H:H").EntireColumn.Hidden = True
      Range("A1").Interior.ColorIndex = 35
  Else
    Cells.EntireColumn.Hidden = False
    Range("A1").Interior.ColorIndex = -1
  End If
End Sub

Insert rows, double click on number in column B, supply new number of rows (#insertrows)

Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) 'D.McRitchie, 2004-09-21, insert more rows up ' until 15, no deletions of rows If Target.Column <> 2 Then Exit Sub If Not IsNumeric(Target) Then Exit Sub Cancel = True Dim i As Long, curv As Long, tov As Long curv = Target.Value tov = InputBox("supply new total rows", _ "Rows input", curv + 1) If tov < curv Then Exit Sub For i = curv + 1 To tov Cells(Target.Row +i-1,1).EntireRow.Insert Cells(Target.Row + i - 1, 3) = i Cells(Target.Row, 2) = i Next i End Sub     For more on inserting rows see insert rows

A B C D  1  Name  Number of Rows  Issue Number  Descr  2  Paul  3   1     3      2     4      3     5  David  2   1     6      2     7  Steven  5   1     8      2     9      3    10      4    11      5    12  Mary  1   1    13  Lisa  3   1    14      2    15      3

Simulate use of the SUM command with an Event Macro with a 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
   Cancel = True   'Get out of edit mode 
   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

Event macro to start an Email with the subject from the the DoubleClicked cell.  More information in Email topic and in the Related area of my Mail Merge page.  Insert CHR(10) within the body for new lines, as needed.  [SendMail, Send E-mail]

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  ThisWorkbook.FollowHyperlink _
      "mailto:dmcritchie@examplex.xcom?subject=" & _
      Replace(Target.Value, "&", "%26") & _
      "&body=This is a test."
End Sub

Event macro to invoke notepad or normal Explorer usage by extension, Full filename or url is in the cell no hyperlinks within.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean) 
   Cancel = True   'Get out of edit mode 
   If LCase(Right("...." & Target.Value, 4)) = ".txt" Then
      Shell "notepad " & Target.Value
   Else
      Shell "explorer.exe " & Target.Value
   End If
End Sub       '// More on use of shell below//

The use of Shell in the above example had some problems after some maintenance fixes, and since starts Internet Explorer minimized.  See example of opening file normalized with a Shell. 

disable Double Click entirely (#disable_dc), see posted reply, Dave Peterson, programming, 2004-09-20.

Examples using Double Click to insert row and propogate formulas.  Examples involving hyperlinks or in place of hyperlinks follow in next section.

Additional examples of date and time used with double-click below change macros in autodate, and on my Date and Time page.

---

Follow Hyperlink Method used within an Event Macro (#followhyperlink)

FollowHyperlink Method to get to webpage   (#followhyperlink).

The Followhyperlink Method provides the ability to open the file and retain use of the BACK and FORWARD web navigation buttons unlike the use of the shell method. 

The use of Followhyperlink allows construction of a hyperlink from concatenating a couple of cells and the string “.hyp” as the file extension.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
   '-- A2:  c:%92inventory%92BinA
   '-- B2:  G0014DU
   '--  c:%92inventory|bina%92goo14du.htm
   Dim hlink As String
   Cancel = True   'Get out of edit mode 
   If Target.Column > 2 Then Exit Sub
   hlink = Cells(Target.Row, 1).Text & "%92" _
     & Cells(Target.Row, 2).Text & ".htm"
   ActiveWorkbook.FollowHyperlink _
     Address:=hlink, NewWindow:=True
End Sub

(The next topic includes Followhyperlink to another Worksheet and additional links to other types of link locations)

FollowHyperlink Method to get to another Worksheet in same workbook   (#followhyperlink_ws)

Go to the name of sheet listed in a cell with a Double Click.  Because you got there with a hyperlink you can return with the BACK button (Alt+ArrowLt).  You can use easily change this to _BeforeRightClick, if you prefer)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
      As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode
  ActiveWorkbook.FollowHyperlink ("'" & Target.Text & "'!A1")
End Sub

Also see sheets for more on hyperlinks, and navigation topic there as well.  These alternatives to object hyperlinks (Ctrl+K) will not change automatically if you rename the worksheets.  (same applies to the event macro above).

  =HYPERLINK("#"&CELL("address",C5),C5) 
  =HYPERLINK("#sheet5", "sheet5")

the second would not pick up a sheetname change in any manner. You might want to enter/use object hyperlink instead (ctrl+k)

< VBA to invoke a Google search or a dictionary search from within an Excel spreadsheet, creates and invokes a hyperlink using FollowHyperlink

ActiveWorkbook.FollowHyperlink NewWindow:=True,
  Address:="http://www.google.com/search?client=googlet&q=" & SearchPhrase

Example to use SHELL to invoke Internet Explorer (#shell)
The following is part of an example for working with broken web links fix404.htm.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim filename As String, IEpath As String
   Cancel = True   'Get out of edit mode 
   IEpath = "C:%92program files%92internet explorer%92iexplore.exe"
   If ActiveCell.Column = 1 Then
     If Right(LCase(ActiveCell.Value), 4) = ".htm" Then
       filename = "c:%92copiedsite%92dmcritchie%92excel%92" _
           & Trim(ActiveCell.Value)
       Cancel = True    '--no need or further need to edit cell
       Shell IEpath & " " & filename, vbNormalFocus
     End If
   & filename, vbNormalFocus
   Else
     Cancel = True     '--no need or further need to edit cell
     Exit Sub
   End If
End Sub

Increment value in Column B on double-click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
       As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  If Target.Row = 1 Then Exit Sub
  If Target.Column <> 2 Then Exit Sub  'Require Col B
  On Error Resume Next
  Application.EnableEvents = False
  Target.Value = Target.Value + 1
  Application.EnableEvents = True
  If Err.Number <> 0 Then
     MsgBox "Unable to add 1 to value in cell " _
       & Target.Address(0, 0)
  End If
End Sub

MATCH Worksheet Function in a macro as a substitute for hyperlinks, in a a name and address list.  (#match)

The entire column A (after the top row) will be looked at and you will get the first match or if not an exact match the highest value alphabetically before the value you are looking for.  Your data must to be sorted when using MATCH with 3rd parameter of 1. 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    '-- Column A after A1 title, must be in Ascending order
    Dim val As String
    Cancel = True   'Get out of edit mode 
    val = "M"   'default value
    val = InputBox("Supply Name", "Supply Name or first " & _
          "few letters of name", val)
    If val = "" Then Exit Sub
    Rows(Application.Match(val, Range("A2:A65536"), 1)).Offset(1, 0).Activate
End Sub

In the posted reply there is also a worksheet_change event macro to select the row on another sheet but had to do that indirectly by calling another macro from the Event macro and not sure why that was necessary. [2003-08-09]

An example of using Double Click to invoke a sort can be found on my sorting.htm page.

Worksheet_FollowHyperlink (#ws_fh)

(one example of a FollowHyperlink Event, but also see examples of FollowHyperlink Method used in email, also see FollowHyperlink above and also on sheets.htm page)

Follow hyperlink in the following example only applies to object hyperlinks, and the Target.Range.Address will return a range for the from instead of a single cell when you assign a bunch of hyperlinks together or use the fill-handle.  So the double-click event macro following may be a lot more practical.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  ' MsgBox "destination: target.subaddress " & Target.SubAddress
  ' MsgBox "Source: Target.Range.Address  " & Target.Range.Address
  ' MsgBox "Source: Target.Range.Value " & Target.Range(1, 1).Value
  Range(Target.SubAddress) = Target.Range(1, 1).Value
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'show row above, and usually some cells to the left, D.McR 2005-12-19
    Dim caddr As String
    caddr = Selection.Address
    On Error Resume Next
    Application.Goto Reference:=Cells(Application.Max(1, _
       ActiveCell.row - 1), 1), Scroll:=True
    Range(caddr).Select
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  Range("A1").Value = Target.Text
  On Error Resume Next
  Range("A1").AddComment
  On Error GoTo 0
  Range("A1").Comment.Visible = False
  Range("A1").Comment.Text Text:="Value from: " & Target.Address(0, 0) _
     & Chr(10) & Format(Now, "ddmmmyyyy hh:mm:ss")
  Cancel = True   ' no further need to edit the cell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row < 5 Then Exit Sub
  Range("A1").Value = Target.Text
  On Error Resume Next
  Range("A1").AddComment
  On Error GoTo 0
  Range("A1").Comment.Visible = False
  Range("A1").Comment.Text Text:="Value from: " & Target.Address(0, 0) _
     & Chr(10) & Format(Now, "ddmmmyyyy hh:mm:ss") & _
     " Selection"
  ' Range("a1").Select  'optional relocation
End Sub

Also see posting by Frank Kabel Frank Kabel and where the poster asked in more than one place my reply

Also see Bill Manville reply to emulate the Worksheet_FollowHyperlink event using Excel 97 2000-08-04.

Worksheet_SelectionChange (#ws_sc)

Worksheet_SelectionChange to prevent entry past a column

The purpose of this macro is that the cursor hits a brick wall at column D (col 4) 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 < 4 then exit sub
   On error resume next  'MUST reenable events...
   Application.EnableEvents = False
   ActiveCell.Offset(1, 1 - Target.Column).Select
   Application.EnableEvents = True
End Sub

A variation to put the selected cell value into the clipboard and mark cell with an indicator color.  Helpful in getting the next value with a TAB (or simply selecting a cell) while filling in an external form one entry at a time. ColorIndex 36

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Column < 4 Then GoTo usevalue
   On Error Resume Next  'MUST reenable events...
   Application.EnableEvents = False
   ActiveCell.Offset(1, 1 - Target.Column).Select
   Application.EnableEvents = True
usevalue:
   ActiveCell.Copy  'Put value into clipboard (marching ants)
   ActiveCell.Interior.ColorIndex = 36
End Sub

Column A selection results in a reversal of whether Column B has an “x” or not

VBA is case sensitive so the value being tested will be enlosed in UCASE(value) to convert to uppercase.

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Column <> 1 Then Exit Sub
   On Error Resume Next  'MUST reenable events...
   Application.EnableEvents = False
   If UCase(Target.Offset(0, 1).Value) = "X" Then
      Target.Offset.Offset(0, 1).ClearContents
   Else
      Target.Offset(0, 1).Value = "x"
   End If
   Application.EnableEvents = True
End Sub

Change FONT color by selection in columns B:E, and Column G (#cyclecolor)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Row = 1 Then Exit Sub   'Do not change Row 1
   If Intersect(Range(Target(1).Address), _
        Range("B:E", "G:G")) Is Nothing Then Exit Sub
   If Target.Font.ColorIndex = 3 Then
      Target.Font.ColorIndex = 1
   Else
      Target.Font.ColorIndex = 3
   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 (#ws_sc2)

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 -- right-click 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.  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.

Populating the first three cells from the row above (#replicate3)

Populating the first three cells of a new row from the previous row.  Solution assumes they are constants.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Row < 3 Then Exit Sub
   If Target.Column <> 1 Then Exit Sub
   If Not IsEmpty(Target) Then Exit Sub
   Application.EnableEvents = False   'should be part of Change macro
   Target.Offset(0, 0).Value = Target.Offset(-1, 0).Value
   Target.Offset(0, 1).Value = Target.Offset(-1, 1).Value
   Target.Offset(0, 2).Value = Target.Offset(-1, 2).Value
   Application.EnableEvents = True   'should be part of Change macro
End Sub

Making the Cursor More Visible (#cursor)

See Chip Pearson’s Rowliner rowliner

Here are some alternatives to making the Cursor more Visible.

1. 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
   

2. 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.
3. 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”. 
4. Otherwise as last resort, use an selection change event macro.
   Highlght Active Cell on Chip Pearson’s Macro page.
5. 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 -- right-click 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#56colors
     Cells.Interior.color = xlAutomatic
     Rows(Target.Row).Interior.colorindex = 3
     Columns(Target.Column).Interior.colorindex = 4
     Target.Interior.colorindex = 8
   End Sub
   

6. a better solution to make the active cell more visible involves changing the borders:
   RowLiner Add-In, Chip Pearson, changes the color of borders left and right of the entire column of the activecell and same for the row of the activecell, choice of color, and width of each border.
   Additional accessibility help for people with disabilities can be found on my Shortcuts page.

Please keep in mind that any macro solution (or addin) will prevent you from using Undo (Ctrl+Z) so you may decide the tradeoff is not worth the loss of being able to use undo.  Keep in mind that you can look at the darkened column letters and Row numbers as well as the address box at the left of the locatioin bar to see where your selection is.

Worksheet_Activate Event   (#ws_activate)

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

An interesting pair of macros to SORT the worksheet upon Sheet Activation or Double-Click can be seen on my Sorting page (topic: #activate).  The Double-Click macro calls the Worksheet_Activate macro providing a second means of invoking the sort.  Technique is ...

Remove use of Volatile (#recalculate)

You can remove the use of Volatile on a Function, if you use Ctrl+alt+f9 yourself to effect a Recalculate all cells on all worksheets in all open workbooks.  To make this a little more automatic you can use an Event macro, but there is no event macro for a lot of things including a Comment change or a change due to a formula.

Private Sub Worksheet_Activate()
  '-- in use to avoid  use of volatile
  Application.CalculateFull  ' ctrl-alt-f9
End Sub
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
      Cancel As Boolean)
  Cancel = True   'get out of edit mode from DoubleClick
  Worksheet_Activate
End Sub

Checkmarks (P) Using the "Wingdings 2" font (#ticks)

Code was posted 2003-06-14 in worksheet.functions  using SelectionChange, but since that is really too sensitive to CPU bound file saving and can’t be changed without leaving cell and returning (TAB and ARROW keys), I think the use of either right-click or Double-Click would be a better choice.  Also since then I realized that the font itself can be use for reference rather than being tied to specific columns (2003-09-18, programming).  If you don’t have "wingdings 2" or prefer a different style of checkmark make another choice of font and letter for checkmarks.

Format the entire column a "wingdings 2" then format the heading row back to your normal font.

You can total the number of checkmarks with =COUNTIF(D2:H2,"D") and if you want a sum of scores you can use the same range =SUM(D2:H2) since they apply to different types of data.

Try each of the following Event macros to see which you prefer and then elimniate the other two.  See use of Cancel = True above. 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True   'Get out of edit mode 
  If LCase(Target.Font.Name) <> "wingdings 2" Then Exit Sub
  If Len(Target.Value) > 1 Then Exit Sub
  If Trim(Target.Value) = "" Then
      Target.Value = "P"
  Else
     Target.Value = ""
 End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  If LCase(Target.Font.Name) <> "wingdings 2" Then Exit Sub
  If Target.Count > 1 Then Exit Sub   'number of cells selected
  If Len(Target.Value) > 1 Then Exit Sub
   If Trim(Target.Value) = "" Then
      Target.Value = "P"
   Else
     Target.Value = ""
  End If
  Cancel = True    'normal RClick menus for cell are suppressed / this sheet
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'.   Paste this Worksheet
' event macro into the module.
'-- Column A should be preformatted as "wingdings 2"
  If Target.Column <> 1 Then Exit Sub  'check for Column A
  If Target.Row = 1 Then Exit Sub
  Application.EnableEvents = False
  If Trim(Target.Value) <> "" Then
     Target.Value = "P"
  Else
     Target.Formula = ""  'clear out cell
  End If
  Application.EnableEvents = True
End Sub

Workbook Events (continued, #workbook2)

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 = True

Start with a specific worksheet when a Workbook is opened.  See BuildTOC for a means of building a Table of Contents page, or a smaller version in BuildTOC2.

Private Sub Workbook_Open()
    On Error Resume Next
    Application.Goto Reference:=Worksheets("$$ TOC").Range("A1"), _
       scroll:=True
    If err = 9 Then  'actually is subscript out of range
        MsgBox "Requested worksheet ""$$ TOC"" was not found " _
          & "by Workbook_Open in ThisWorkbook."
    End If
    Worksheets("Menu Sheet").Activate
    Sheets("$$ TOC").Move Before:=Sheets(1) 'insure is 1st sheet
    On Error GoTo 0     'only needed if you have more code
End Sub

Workbook Sheet Activate (#wb_sheetactivate)

The following will update a list of worksheets in the workbook, omiting worksheets named “Master” and “ListOfSsheets”.  A named list (wslist) will be created that can be used where a list of worksheet names might be wanted as for Conditional Formatting.

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim i As Long
  Dim wkSheet As Worksheet
  With Sheets("ListOfSheets")
   .Range("A:A").Clear
   i = 0
   For Each wkSheet In Application.Worksheets
     If LCase(wkSheet.Name) <> "master" _
          And wkSheet.Name <> .Name Then
       i = i + 1
       .Cells(i, 1) = wkSheet.Name
     End If
   Next wkSheet
   ActiveWorkbook.Names.Add _
     Name:="wslist", RefersTo:=.Range("A1:A" & i)
  End With
End Sub

Place this formula in the workbook: (Frank Kabel, 2004-05-23)
  =SUMPRODUCT(COUNTIF(INDIRECT("'" & wslist&"'!A1:A100"),A1))>1  -- enter as array formula  Ctrl+Shift+Enter
The formula is not working for me will have to look into this further.

The following will goto the row whichever of A,B,C goes down the furthers and will activate the cell in Column A of that row.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Dim R As Long
   'pick the longest of the first 3 columns, and select in the A column
   R = Application.WorksheetFunction.Max(Cells(Rows.Count, 1).End(xlUp).row, _
        Cells(Rows.Count, 2).End(xlUp).row, _
        Cells(Rows.Count, 3).End(xlUp).row)
   Cells(R, 1).Select
End Sub

Workbook_SheetFollowHyperlink (#wb_followhyperlink)

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
        ByVal Target As Hyperlink)
    Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

My own preference if I don't like the position would be to use something like the ShowTopLef5 macro (but ONLY occasionally used).  There are several ActiveWorkbook.FollowHyperlink examples in buildtoc.htm having to do with opening an HTML link.

Actually the macro above is not triggered from the HYPERLINK Worksheet Function.  So activating the worksheet might be a better solution in that case.  Added the line to exit if the activation is to the first worksheet tab.&nbsb;

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sheets(1).Name = ActiveSheet.Name Then Exit Sub
    Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

Workbook NewSheet (#wb_newsheet)

Example to force a newly created worksheet to the last worksheet tab (Chip Pearson and Tom Ogilvy 2004-02-15, misc). Install in ThisWorkbook module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Sh.Move After:=ThisWorkbook.Sheets( _
      ThisWorkbook.Sheets.Count)
End Sub

Workbook Open (#wb_open)

• Example: open to a specific worksheet
• Create a short table of contents on workbook open, include use of followhyperlink to another worksheet via a doubleclick event macro. also included on this page.

Workbook SheetChange (#wb_shtChange)

Workbook SheetChange 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

Run a macro on sheet change

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If LCase(ActiveSheet.Name) = "master" Then GoTo done
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    'note if this macro or the macro it calls fails you
    '  will have to reenable events in a macro or in Intermediate
    '     application.enableevents = True
   On Error GoTo aft_error
   'MsgBox "testing   sorting_macro  would be executed"
   Call Sorting_macro   'must be in this workbook
aft_error:
   With Application
        .EnableEvents = True
        .ScreenUpdating = True
   End With
done:
End Sub

Fixup the Existing Workbook Entries

Run the following from a standard 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

Have no idea what this macro is doing here, I have much better macros on Proper Case and other case changes including event macros.

Change Name of Worksheet to match that of cell A1

 Private Sub Workbook_SheetChange(ByVal Sh As Object, _
      ByVal Target As Range)  'D.McRitchie, misc, 2002-10-12'
    If Target.Address <> "$A$1" Then Exit Sub
    If Trim(Target.Address) = "" Then Exit Sub
    On Error Resume Next
    Sh.Name = Range("A1").Value
    If Err.Number <> 0 Then
      MsgBox "Attempt to Rename sheet to Cell value" _
       & " in A1 failed" & Chr(10) & Chr(10) & _
       Err.Number & " " & Err.Description
    End If
    On Error GoTo 0
 End Sub
 

Miscellanous examples of Worksheet Change macro (#chgmisc)

• Changing Font Color for New Text (D.McR, 2005-07-24, misc), Event macro to change Font color in Column A (and interior color as well), Reset with double-click in cell A1.

HELP Topics in VBE (#helptopics)

Application Object Events

Workbook Object Events

Worksheet Object Events

 - SelectionChange Event

Event Statement, example

MouseDown Event, example

Problems   (#problems)(reenable)

Macros will not run if the security setting is set to High, Check under Tools , Macro, security, set to Medium.  If the security setting is set a High and you try to run macros in the workbook you will see the following message:
  The macros in the project are disabled.  Please refer to the online help or documentation of the host application to determine how to enable macros.

Make sure you are not in break mode following macro failure.  If you are then fix the error and press the square Reset button in the VBE (Visual Basic Editor).  so that you can retest your macro, or press the Run (Run Sub/User Form) button that looks like the Play button on a VCR to resume macro execution.

The main problem that people first experience with event macros is installing them in a standard module where they will simply be ignored.  Worksheet event macros are installed with the worksheet class module, and workbook event macros are installed into ThisWorkbook class module.  Installed in the wrong place they will be ignored.

Note the Change Event macros that change data should include code to turn off EnableEvents to prevent triggering another Change Event.  Once set to False you cannot run another Event macro nor can you run you Event macro after you fix a problem with your Event macro.  You would have to run a standard macro to put reenable events by putting EnableEvents back to True.

Sub Fix_Things()
   Application.DisplayAlerts = True 
   Application.EnableEvents = True   'should be part of Change macro
End Sub

A more complete macro can be seen in the reset_things macro in
   http://www.mvps.org/dmcritchie/excel/code/proper.txt

If you prefer you can use the intermediate window (Ctrl+G) of the VBE
  testing first may help you determine if there was a problem, and perhaps help you narrow a problem down to when it occured.
  To Test if you had a problem by typing this line into the Intermediate Window.
    Application.EnableEvents
  then if it shows False fix it, by typing this into the Intermediate Window
    Application.EnableEvents = True

If you are not working with an Worksheet event macro (Worksheet_)  or a Workbook event macro (workbook_)  or other Event macro (chart_, app_),  additional VBE macros installed in ThisWorkbook (VBE Objects, VBProject, VBComonent, CodeModule) are installed in ThisWorkbook see Coding for the VBE (last two links are Chip Pearson's)
Otherwise, you should be installing as a standard macro, see Getting Started with macros which also lists a few additional problems you could encounter with macros.

Related Information   (#related)

• BuildTOC, Using an Event to suppress generation of hyperlinks.  Also see Using an Event Handler to disable automatic hyperlinks (McGimpsey)it is an Event macro that will be invoked. (MS KB 233073 is defunct)
• Calculate event,  see related area in Highlight Change for links to Calculate event.
• Cell Comments,  Adding / Changing,  user identification into cell comments when a change is made.
• A 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.
• Collections, An Event macro to help with displaying all of the right-click menus in support of an example from Jim Rech.
• Disable the right-click context menu on a cell with Cancel = True to get out of edit mode (see right-click above.  To disable the right-click menu on a cell use   Application.Commandbars("cell").Enabled = True    To disable the right-click menu on a worksheet tab use   Application.Commandbars("Ply").Enabled = False
• 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.
• Multiplication Quiz, set up entirely with Event macros (rightclick, doubleclick, change).
• New Workbook Event, Chip Pearson, 2003-04-14, programming.
• ONTIME, Scheduling Procedures With OnTime, Chip Pearson, Using VBA, you can call upon the OnTime method of the Excel Application object to instruct Excel to run a procedure at a given time. By writing your code to call the OnTime method by itself, you can have VBA code automatically execute on a periodic basis.
• pathname, Workbook_BeforePrint event.
• Proper, Worksheet change to convert a column to capitals such as US zip state code when entered.
• Removing all macros in a workbook, involve removing all modules, and all code associated with worksheets, and ThisWorkBook.  More information and a programming code to effect this at Chip Pearson’s Coding for the VBE (Visual Basic Editor).
   
• Right Click Menus (Context Menus) in Excel -- Also see Toolbars page.  key phrases: Application.CommandBars("Cell").Controls, !CopyFormula, !PasteFormula, ThisWorkBook, Add(temporary:=True)
   
• Shortcuts, clicks, in Excel and other applications:  Right-click:  Excel, IE;  Short Cuts (keyboard shortcuts):  Excel; IE, OE, Win2000;  Toolbars:  Excel;  Drag/Fillhandle: Excel Fill Handle, Windows drag files;   Accessibility Options: MouseKeys; Event Macros: Excel (this page)
   
• 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.
• Typing over a formula and retaining the formula.  You can't, of course, but Hank Scorpio has provided a detailed answer.

For more information about Change Events see:

• Application Level Events, Chip Pearson
• Event Procedures In Microsoft® Excel97® Chip Pearson, including new topic “Order of Events”
• 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.

Additional Examples of Change Event macros

• Coloring Capital Letters within a cell, posting by Mike Currie, 2002-12-19, misc, colors capitals Red [3], non-capitals as black [lowercase and digits with 1] and error cells with Teal [14] using a Change Event macro, I included a comparable ColorCapsCatchUp macro for use on existing cells prior to installing the Change Event macro.

For those with money, or a department budget, or who just hang out at bookstores w/o buying anything.  (You won’t be able to use the CD-ROM in a bookstore)

• 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)

• 213220 - XL2000: Summary of Workbook and Worksheet Application Events, references to other versions also: 161761 - XL97, 291294 - XL2002

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

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2009,  F. David McRitchie,  All Rights Reserved