AfterUpdate event from a dropdown list? (2003)

[jswedlund]

I'm setting up a template that contains a dropdown, Data|Validation|List, with values from a range name. I want to run a procedure that is in Module1 and pass the value of the selection from the dropdown cell. Or, how can I call an AfterUpdate event proc (like in Access) when a choice is made from the dropdown. Also, where will or should that event proc live? In the Sheet code, Workbook, module...? I am comfortable in the Access VBA environment but not so in Excel.

[HansV]

You can use the Worksheet_Change event in the worksheet module for this. Let's say that the cell with the validation dropdown is D5. The code could look like this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("D5"), Target) Is Nothing Then
    Call MyProcedure(Range("D5").Value)
  End If
End Sub

where MyProcedure is the name of the procedure in Module1.

[jswedlund]

Thank you, that's what I needed. So, individual cells don't have events associated with them, correct?

[HansV]

That is correct, but the Worksheet_Change event occurs when one or more cells have been changed, and it has a Target argument that is the range of all cells that have been changed. So you can react to cells being changed by using the Worksheet_Change event.

Similarly, the Worksheet_SelectionChange event occurs when the user selects a different range. It also has a Target argument that represents the new selection.

[jswedlund]

Thanks again Hans, this time for the explanation. I had come across this once some time ago but could not recall it.