Dear Rick,

What a pleasure to work with people like you : you don't give up and keep
digging !

The situation is as follows:

a) I use to develop ONLY with Excel 97 (SR2) : the very reason is that I am
always sure my customers will have a working solution on their version.
b) Nevertheless, I do own Excel 2000 (9.0.2720) and Excel 2007.
c) Your first solution works on all versions BUT NOT on Excel97.
d) Your last post generates errors in all versions although the error
notification varies from version to version.

So, I scratched some code yesterday to put it at work on ALL versions:
here is my code, in case someone would want to see or would need it.

a) The workbook has a worksheet named "Clients" on which the headers a
Col A Idx and col B Names
b) The Userform is named frmSelector and has
one Textbox named TextBox1
one Listbox named ListBox1
one Button named btnExit with the Caption="EXIT"

Here's the code for the Userform:
'UserForm frmSelector
Option Explicit

'Declarations (frmSelector Code Level)
Dim str_Clients() As String
Dim lng_LastRow As Long

Private Sub btnExit_Click()
frmSelector.Hide
End Sub

Private Sub UserForm_Initialize()
Dim lngLoopPtr As Long
'Dim lng_LastRow As Long
Const lngconstStartRow As Long = 2
Const str_constNamesColumn As String = "B"
frmSelector.TextBox1.Text = ""
frmSelector.TextBox1.EnterKeyBehavior = True
With Worksheets("Clients")
lng_LastRow = .Cells(Rows.Count, str_constNamesColumn).End(xlUp).Row
ReDim str_Clients(0 To lng_LastRow - lngconstStartRow + 1)
For lngLoopPtr = lngconstStartRow To lng_LastRow
str_Clients(lngLoopPtr - lngconstStartRow) = .Cells(lngLoopPtr,
str_constNamesColumn)
frmSelector.ListBox1.AddItem .Cells(lngLoopPtr, str_constNamesColumn)
Next
End With
frmSelector.TextBox1.SetFocus
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
With frmSelector.TextBox1
If KeyCode = vbKeyLeft Then
frmSelector.ListBox1.ListIndex = -1
.SelStart = Len(.Text)
.SetFocus
ElseIf KeyCode = vbKeyReturn Then
.Text = frmSelector.ListBox1.List(frmSelector.ListBox1.Lis tIndex)
.SelStart = Len(.Text)
.SetFocus
End If
End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmSelector.TextBox1.Text =
frmSelector.ListBox1.List(frmSelector.ListBox1.Lis tIndex)
lng_MatchingRow = WorksheetFunction.Match(frmSelector.TextBox1.Value ,
Sheets("Clients").Range("B1:B" & lng_LastRow), 0)
btnExit_Click
End Sub

Private Sub TextBox1_Change()
Dim lngLoopPtr, lngLoopPtr2 As Long
Dim lngListHeight As Long
Dim str_Individual As String
Dim str_Persons() As String
Dim int_InpLen As Integer
int_InpLen = Len(frmSelector.TextBox1.Text)
frmSelector.ListBox1.Clear
For lngLoopPtr = 0 To lng_LastRow - 1
If Left(str_Clients(lngLoopPtr), int_InpLen) =
UCase(frmSelector.TextBox1.Text) Then
frmSelector.ListBox1.AddItem str_Clients(lngLoopPtr)
End If
Next lngLoopPtr
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
With frmSelector.ListBox1
If KeyCode = vbKeyReturn Then
KeyCode = 0
If .ListCount = 1 Then
frmSelector.TextBox1.Text = .List(0)
frmSelector.TextBox1.SelStart = Len(frmSelector.TextBox1.Text)
Else
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
ElseIf KeyCode = vbKeyDown Or (KeyCode = vbKeyRight And _
.ListCount 0 And frmSelector.TextBox1.SelStart =
Len(frmSelector.TextBox1.Text)) Then
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
End With
End Sub

And, for the project, I also have a module named modSelector :
Here's the code for modSelector:
'MODULE modSelector
Option Explicit

'Declarations
Public lng_MatchingRow As Long
Public zut As String



Sub Main()
frmSelector.Show
MsgBox "Client #" & Worksheets("Clients").Range("A" & lng_MatchingRow) & _
" = " & _
Worksheets("Clients").Range("B" & lng_MatchingRow)
End Sub



=== the code as such works in All versions of Excel

I really thank you for this exercice which helped me understand namely how
to get the last row and the defined Names

Such a discussion 'empowers' and I'll always be glad to cooperate.
Thanks a lot for your precious time.
Very best regards,
Herve+
--
Herve Hanuise
http://www.affordsol.be