Using EVENT Processing in VBA

Engineering Software general discussion VBA
, ,
#1

There are various Chart, Worksheet and Workbook Events in Excel, the Document Event in Word, Application Events in Outlook (nothing that I can find in Power Point other than UserForms). And UserForms also have Events.

There is a skeleton procedure already set up for each Event. Here’s how to find these Event Procedure Skeletons…

  1. alt+F11 activates the Visual BASIC Editor

  2. In the Visual BASIC Editor ctrl+R - activates the Project Explorer

  3. In the Project Explorer, Open Microsoft Excel Objects

  4. Right click the object of choice (Chart, Worksheet or Workbook) and select View Code from the Pop-Up Menu

  5. Select the Object DropDown in the upper left-hand corner of the Code Window (F7 activates Code Window)

  6. From the Object DropDown, select either Chart, Workbook or Worksheet Object.

  7. Select the Procedure DropDown in the upper right-hand corner of the Code Window

  8. From the Procedure DropDown, select the Event that you want to control.

  9. In the Event Skeleton in the Code Window, enter your code.

Here’s an example I just finished working on for a user.
Question: I have a list in Sheet2 that I’m using as a source for a Data Validation LIST Drop Down In Sheet1. What I need is for the Drop Down Selection to be linked to the location in the source list, so that if the value in the list changes, the value displayed in my Data Validation Selection also changes.

Answer: In order for that to happen, the Data Validation Selection VALUE would need to refer back to the source list with a formula like…
=INDEX(SourceRange,SourceRow)

Enter Event Processing!
This is a job for a Worksheet Change Event
, that detects when DATA changes in a Worksheet Object, like a Range.
Here are some factors we want to consider…

  1. We only want this EVENT process to run in a defined range.
  2. If we replace the VALUE with a FORMULA that will return the same value that is currently in the Source List Row, since the DATA in the cell is actually changing from a VALUE to a FORMULA, we don’t want the EVENT to fire again.
  3. We want to make the formula as general as possible without referring to any specific value or range.
  4. Name the Lookup Range, using the Heading Value via Formulas>Defined Names>Create from Selection>Create Names from…Top row.
  5. Each time a change occurs in the Lookup Range, redefine the Named Range in a Worksheet Change Event Procedure.

Event Processing acts on specific Objects, in this case, within a Workbook. Since this is a Worksheet Event, if you Right-Click on a Sheet TAB, you’ll get a Pop-Up menu that contains a View Code Item that will display the Code Window for the Worksheet in the VBA Editor (VBE).

alt+F11 toggles between the Sheet and the VBE.

In the VBE, ctrl+R displays the Project Explorer. It displays all the Workbook and Sheet Objects in your Workbook. If you have other Workbooks open in the same instance of Excel, you will see those Objects as well.

Above the Code Window are two Drop Downs: Object and Procedure. If you are in the Code Window for a Sheet, then the Object Drop Down displays (General), Worksheet. The Procedure Drop Down lists all the Procedures (Subs & Functions) for what is Selected in the Object Drop Down.

In the uploaded Workbook,
se-DataValidationLinkedValue.xlsm,
there are 2 Event Procedures: in DataValidation and Lookup. They are both for Worksheet_Change Events.

Here is the VBA for these 2 Event Procedures…

DataValidation Object Code
Private Sub Worksheet_Change(ByVal Target As Range)

'SkipVought 2020 Aug 16
'Data Validation LOOKUP that
’ replaces the LOOKUP VALUE for a LOOKUP FORMULA
’ and uses the LOOKUP VALUE ROW OFFSET

'NOTE: Make sure that your lookup range is a NAMED RANGE
’ and that NAMED RANGE is used in the DataValidation LIST spec.
’ That way you need not be concerned for any sheet name.

Dim lLookupROW As Long, sLookupRangeNAME As String, rLookupRANGE As Range

'Worksheet_Change in Column D only

If Not Intersect(Target, Range("D:D")) Is Nothing Then
    'DataValidation FORMULA
    sLookupRangeNAME = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
    'convert string to Range Object
    Set rLookupRANGE = Evaluate(sLookupRangeNAME)
    'lookup row
    lLookupROW = Application.Match(Target.Value, rLookupRANGE, 0)
    
    'turn off EVENTS-don't want the procedure to fire
    ' when the VALUE is replaced with the FORMULA
    Application.EnableEvents = False
    'replace lookup VALUE with FORMULA
    Target.Formula = "=INDEX(" & sLookupRangeNAME & "," & lLookupROW & ")"
    'turn on EVENTS
    Application.EnableEvents = True
    
End If

End Sub

Lookup Object Code
Private Sub Worksheet_Change(ByVal Target As Range)

'SkipVought 2020 Aug 17
'This event assures that the table (assuming that the table is the only data on this sheet)
’ with unique headings in row 1, are all NAMED RANGES

With ActiveSheet.UsedRange
    Application.DisplayAlerts = False
    .CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
    Application.DisplayAlerts = True
End With

End Sub

Help yourself to this very brief Event Process example Workbook. I welcome your questions and comments.
se-DataValidationLinkedValue.xlsm (23.6 KB)

2 Likes