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

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

Does this work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InStr(Target, ":") > 0 Then
FindData = Left(Target, InStr(Target, ":") - 1)
Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Target = c.Offset(0, 1)
End If
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

Hi Joel -- thanks for picking this up.

the short answer is "no". I get a Type Mismatch on the first line
If InStr(Target, ":") > 0

Also, I think this code will only work if the target cell is part of a range
i.e. A1:B1 as it's looking for the colon. If the cell is a single cell i.e.
A1 only then I presume I'd need to include and Else statement in the IF. e.g.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InStr(Target, ":") > 0 Then
FindData = Left(Target, InStr(Target, ":") - 1)
Else
FindData = Target.AddressLocal(RowAbsolute:=False,
ColumnAbsolute:=False)
End If

Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Target = c.Offset(0, 1)
End If
End Sub

Does your code return an absolute cell address? I need it to return A1
rather than $A$1

And, last of all, if the cell address is not in the tblHELPTXT then I need
to return a "no help" message to the text box.

Thanks again.

Trevor

"Joel" wrote:

> Does this work?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If InStr(Target, ":") > 0 Then
> FindData = Left(Target, InStr(Target, ":") - 1)
> Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _
> LookIn:=xlValues, lookat:=xlWhole)
> If Not c Is Nothing Then
> Target = c.Offset(0, 1)
> End If
> 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

Hi Joel -- I think I've cracked it.
Let me know if you think I could refine it.
Thanks
Trevor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myAddress = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
If InStr(myAddress, ":") > 0 Then
FindData = Left(myAddress, InStr(myAddress, ":") - 1)
Set c = Sheets("Sheet1").Range("tblHELPTXT").Resize(,
1).Find(what:=FindData, _
LookIn:=xlValues, lookat:=xlWhole)
Else
Set c = Sheets("Sheet1").Range("tblHELPTXT").Resize(,
1).Find(what:=myAddress, _
LookIn:=xlValues, lookat:=xlWhole)
End If

If Not c Is Nothing Then
MsgBox (c.Offset(0, 1))
Else
MsgBox ("No Help")
End If

End Sub


"Joel" wrote:

> Does this work?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If InStr(Target, ":") > 0 Then
> FindData = Left(Target, InStr(Target, ":") - 1)
> Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _
> LookIn:=xlValues, lookat:=xlWhole)
> If Not c Is Nothing Then
> Target = c.Offset(0, 1)
> End If
> 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