entering value in column A - filling in column B automatically

[steph]

Hello,

My users requested this functionality:
They enter values in, say, column A. Column B should be filled
automatically accordingly by executing some VBA-code (querying a
database).
Problem is that column A also might be filled by VBA-code - meaning
that above functionality only should trigger if the user fills in
column A. (I wonder if if is possible to differentiate between these
cases).

I'm not quite sure if I should use a Worksheet_Change() or
Worksheet_SelectionChange() event. Or is there another more
appropriate way to handle this requirement?

We're using Office 2007.

thanks for any input,
stephan

 

[Dave Peterson]

You could use the worksheet_Change event to capture the change to the cells in
column A.

But you'll have to do something to the existing code that makes changes to that
column--you could use "application.enableevents = false" to tell excel to ignore
your code based changes--or use your own (public??) variable.

This is my sample worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToCheck As Range
Dim myIntersection As Range
Dim myCell As Range
Dim res As Variant
Dim LookUpRng As Range

Set RngToCheck = Me.Range("A:A")

Set myIntersection = Intersect(RngToCheck, Target)

If myIntersection Is Nothing Then
Exit Sub
End If

With Worksheets("Sheet2")
Set LookUpRng = .Range("A:b")
End With

Application.EnableEvents = False
For Each myCell In myIntersection.Cells
If IsEmpty(myCell.Value) Then
'skip it
Else
res = Application.VLookup(myCell.Value, LookUpRng, 2, False)
If IsError(res) Then
myCell.Offset(0, 1).Value = "No match!"
Else
myCell.Offset(0, 1).Value = res
End If
End If
Next myCell
Application.EnableEvents = True

End Sub

And in any other code...

Option Explicit
Sub testme()
Application.EnableEvents = False
Worksheets("Sheet1").Range("a1").Value = "hi there"
Application.EnableEvents = True
End Sub