Maybe...

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim res As Variant
Dim myTable As Range

With Worksheets("sheet9999")
Set myTable = .Range("tblhelptxt")
End With

Set Target = Target.Cells(1) 'first cell

res = Application.VLookup(Target.Value, myTable, 2, False)

'still want this?
Me.Range("A3").Value = Target.Address(0, 0)

'and maybe this
Me.Range("B3").Value = Target.Value

If IsError(res) Then
Me.Range("C3").Value = "Not found"
Else
Me.Range("C3").Value = res
End If

End Sub





Trevor Williams wrote:

Hi All

I have a sheet that contains a textbox that displays help text depending on
the selected cell.

Currently the text is selected via a lookup formula based on another cells
value. This cell is updated via the Worksheet_SelectionChange event -- so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A3") = Target.AddressLocal(RowAbsolute:=False,
ColumnAbsolute:=False)
End Sub

Then Cell C3 uses the following formula to lookup the text:
=VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE)

Is there a way to do this lookup in the Worksheet_SelectionChange event
rather than being reliant on cells updating?

I'm using XL2002

Look forward to your responses.

Trevor Williams

--

Dave Peterson