Selection based on partial input
 

Hi to all,

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the textbox,
and, as each new typed character comes in, I want the listbox to display (in
full) all of the names on sheet1 beginning with the actual content of the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name of
the listbox.

How can I code that in vba ??


Thanks by advance for your help,
Herve+
--
Herve Hanuise
http://www.affordsol.be

Selection based on partial input
 

On Mar 15, 6:46 am, affordsol
wrote:
Hi to all,

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the textbox,
and, as each new typed character comes in, I want the listbox to display (in
full) all of the names on sheet1 beginning with the actual content of the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name of
the listbox.

How can I code that in vba ??

Thanks by advance for your help,
Herve+
--
Herve Hanuisehttp://www.affordsol.be

Here you go:

Private Sub TextBox1_Change()

'Clear previous entries
UserForm1.ListBox1.Clear

Dim strPartial As String
strPartial = UserForm1.TextBox1.Text

Sheets("Sheetname").Activate
Cells(1, 1).Select

Do Until ActiveCell = ""
If Left(ActiveCell, Len(strPartial)) = strPartial Then
UserForm1.ListBox1.AddItem (ActiveCell)
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub


Works for me :)

Chris

Selection based on partial input
 

On Mar 15, 8:34 am, cht13er wrote:
On Mar 15, 6:46 am, affordsol
wrote:



Hi to all,

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the textbox,
and, as each new typed character comes in, I want the listbox to display (in
full) all of the names on sheet1 beginning with the actual content of the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name of
the listbox.

How can I code that in vba ??

Thanks by advance for your help,
Herve+
--
Herve Hanuisehttp://www.affordsol.be

Here you go:

Private Sub TextBox1_Change()

'Clear previous entries
UserForm1.ListBox1.Clear

Dim strPartial As String
strPartial = UserForm1.TextBox1.Text

Sheets("Sheetname").Activate
Cells(1, 1).Select

Do Until ActiveCell = ""
If Left(ActiveCell, Len(strPartial)) = strPartial Then
UserForm1.ListBox1.AddItem (ActiveCell)
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Works for me :)

Chris

Oops, if it's just names you want then select cells(1,2) .... and if
you're not sure that you'll never have a "" before the list of names
is done, you might want to change the loop (something like this:
Sheets("Data2").Activate
ActiveSheet.UsedRange
LastRow = Cells.SpecialCells(xlLastCell).Row )

Chris

Selection based on partial input
 

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the textbox,
and, as each new typed character comes in, I want the listbox to display
(in
full) all of the names on sheet1 beginning with the actual content of the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name of
the listbox.

How can I code that in vba ??

First, thank you for asking this question... I found this to be a fun coding
project to develop.

Okay, give the code after my signature a try (copy/paste all of the code
into the UserForm's code window). It assumes your TextBox is named TextBox1
and your ListBox is named ListBox1. Also, it assumes that your Names list
starts in Row 2 of Column B. Both of these are set via Const statements in
the UserForm's Initialize event; so you can change these to suit your actual
conditions if necessary. I did not provide for the Exit button since I'm
assuming you already know how to do that and because I don't know what code
you wish to execute when exiting the UserForm.

There are a couple of "hidden" features you may find interesting (these are
geared to letting you continue to work from the keyboard while typing).
Pressing the down arrow (or Right Arrow key when at the end of the text you
are typing) when in the TextBox will move you into the ListBox where you can
cursor up or down to the name you want and then press the Enter key to
select that name (and, of course, you can double-click on the name to select
it if that is your preference instead). Also, if only one name remains in
the list, pressing Enter selects it into the TextBox directly (in the same
way as if you had double-clicked on it). If, once in the ListBox, you decide
you would like to return to the TextBox, just press the Left Arrow key and
you will find yourself back in the TextBox at the end of the text that you
have been typing.

Okay, that is pretty much it. Let me know if this works for you or not (and
if not, tell me what changes you need or want).

Rick

Dim TextArray() As String

Private Sub UserForm_Initialize()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const NamesColumn As String = "B"
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, NamesColumn).End(xlUp).Row
ReDim TextArray(0 To LastRow - StartRow + 1)
For X = StartRow To LastRow
TextArray(X - StartRow) = "@" & .Cells(X, NamesColumn)
ListBox1.AddItem .Cells(X, NamesColumn)
Next
End With
End Sub

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

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
End Sub

Private Sub TextBox1_Change()
Dim X As Long
Dim ListHeight As Long
Dim Individual As String
Dim Persons() As String
Persons = Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UBound(Persons) -1 Then
With ListBox1
.Clear
For X = 0 To UBound(Persons)
.AddItem Mid$(Persons(X), 2)
Next
End With
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
End Sub

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

Selection based on partial input
 

Hello Rick,

Thanks a lot for your interest in my problem.

I pasted your code accordingly but I get a "Complie error : undefined Sub or
Function" message when the code reaches the line
Persons = Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)

FILTER is the problem here !


What do you suggest ???


Thanks a lot and regards from Belgium,
Herve+
--
Herve Hanuise
http://www.affordsol.be


"Rick Rothstein (MVP - VB)" wrote:

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the textbox,
and, as each new typed character comes in, I want the listbox to display
(in
full) all of the names on sheet1 beginning with the actual content of the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name of
the listbox.

How can I code that in vba ??

First, thank you for asking this question... I found this to be a fun coding
project to develop.

Okay, give the code after my signature a try (copy/paste all of the code
into the UserForm's code window). It assumes your TextBox is named TextBox1
and your ListBox is named ListBox1. Also, it assumes that your Names list
starts in Row 2 of Column B. Both of these are set via Const statements in
the UserForm's Initialize event; so you can change these to suit your actual
conditions if necessary. I did not provide for the Exit button since I'm
assuming you already know how to do that and because I don't know what code
you wish to execute when exiting the UserForm.

There are a couple of "hidden" features you may find interesting (these are
geared to letting you continue to work from the keyboard while typing).
Pressing the down arrow (or Right Arrow key when at the end of the text you
are typing) when in the TextBox will move you into the ListBox where you can
cursor up or down to the name you want and then press the Enter key to
select that name (and, of course, you can double-click on the name to select
it if that is your preference instead). Also, if only one name remains in
the list, pressing Enter selects it into the TextBox directly (in the same
way as if you had double-clicked on it). If, once in the ListBox, you decide
you would like to return to the TextBox, just press the Left Arrow key and
you will find yourself back in the TextBox at the end of the text that you
have been typing.

Okay, that is pretty much it. Let me know if this works for you or not (and
if not, tell me what changes you need or want).

Rick

Dim TextArray() As String

Private Sub UserForm_Initialize()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const NamesColumn As String = "B"
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, NamesColumn).End(xlUp).Row
ReDim TextArray(0 To LastRow - StartRow + 1)
For X = StartRow To LastRow
TextArray(X - StartRow) = "@" & .Cells(X, NamesColumn)
ListBox1.AddItem .Cells(X, NamesColumn)
Next
End With
End Sub

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

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
End Sub

Private Sub TextBox1_Change()
Dim X As Long
Dim ListHeight As Long
Dim Individual As String
Dim Persons() As String
Persons = Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UBound(Persons) -1 Then
With ListBox1
.Clear
For X = 0 To UBound(Persons)
.AddItem Mid$(Persons(X), 2)
Next
End With
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
End Sub

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

Selection based on partial input
 

What version of Excel are you using; actually, more importantly, what
version of VB is it (in the VB editor, Help/About Microsoft Visual Basic)?
I'm guessing your version of VB is less than 6.0 (I think things like
Filter, Split, Join, etc. were added then). If it turns out you have an
earlier version, I think I can dummy-up some coded functions to replace what
are built-in functions in my version. Let me know and I'll try and give you
an estimate of how long it might take me to do it. It's kind of late here
now and I'll being going to sleep shortly, so I'll read your reply and let
you know later today.

Rick


"affordsol" wrote in message
...
Hello Rick,

Thanks a lot for your interest in my problem.

I pasted your code accordingly but I get a "Complie error : undefined Sub
or
Function" message when the code reaches the line
Persons = Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)

FILTER is the problem here !


What do you suggest ???


Thanks a lot and regards from Belgium,
Herve+
--
Herve Hanuise
http://www.affordsol.be


"Rick Rothstein (MVP - VB)" wrote:

In an Excel workbook, sheet1 has a list of the customer names :
col1=idx and col2=name

I need to build up a user form with
one Textbox
one Listbox
one Button (Exit)

I need to enable the user to input the beginning of a name in the
textbox,
and, as each new typed character comes in, I want the listbox to
display
(in
full) all of the names on sheet1 beginning with the actual content of
the
textbox to be listed in the listbox.
The final selection is then done by a double click on the choosen name
of
the listbox.

How can I code that in vba ??

First, thank you for asking this question... I found this to be a fun
coding
project to develop.

Okay, give the code after my signature a try (copy/paste all of the code
into the UserForm's code window). It assumes your TextBox is named
TextBox1
and your ListBox is named ListBox1. Also, it assumes that your Names list
starts in Row 2 of Column B. Both of these are set via Const statements
in
the UserForm's Initialize event; so you can change these to suit your
actual
conditions if necessary. I did not provide for the Exit button since I'm
assuming you already know how to do that and because I don't know what
code
you wish to execute when exiting the UserForm.

There are a couple of "hidden" features you may find interesting (these
are
geared to letting you continue to work from the keyboard while typing).
Pressing the down arrow (or Right Arrow key when at the end of the text
you
are typing) when in the TextBox will move you into the ListBox where you
can
cursor up or down to the name you want and then press the Enter key to
select that name (and, of course, you can double-click on the name to
select
it if that is your preference instead). Also, if only one name remains in
the list, pressing Enter selects it into the TextBox directly (in the
same
way as if you had double-clicked on it). If, once in the ListBox, you
decide
you would like to return to the TextBox, just press the Left Arrow key
and
you will find yourself back in the TextBox at the end of the text that
you
have been typing.

Okay, that is pretty much it. Let me know if this works for you or not
(and
if not, tell me what changes you need or want).

Rick

Dim TextArray() As String

Private Sub UserForm_Initialize()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const NamesColumn As String = "B"
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, NamesColumn).End(xlUp).Row
ReDim TextArray(0 To LastRow - StartRow + 1)
For X = StartRow To LastRow
TextArray(X - StartRow) = "@" & .Cells(X, NamesColumn)
ListBox1.AddItem .Cells(X, NamesColumn)
Next
End With
End Sub

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

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
End Sub

Private Sub TextBox1_Change()
Dim X As Long
Dim ListHeight As Long
Dim Individual As String
Dim Persons() As String
Persons = Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UBound(Persons) -1 Then
With ListBox1
.Clear
For X = 0 To UBound(Persons)
.AddItem Mid$(Persons(X), 2)
Next
End With
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
End Sub

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

Selection based on partial input
 

Thanks a lot for your interest in my problem.

I pasted your code accordingly but I get a "Complie error : undefined
Sub or Function" message when the code reaches the line Persons =
Filter(TextArray, "@" & TextBox1.Text, True, vbTextCompare)

FILTER is the problem here !

What do you suggest ???

Forget my other post... I'll assume you have an earlier version of Excel/VBA
(which does not support the Filter function) and just give you code that
should work on your system. I removed the call to VBA6's Filter function,
substituted one I found online (see the comments for attributions) and
changed my code to make use of it. Delete all the code I gave you earlier
and copy/paste all the code following my signature instead.

Rick

Dim TextArray() As String

Private Sub UserForm_Initialize()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const NamesColumn As String = "B"
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, NamesColumn).End(xlUp).Row
ReDim TextArray(0 To LastRow - StartRow + 1)
For X = StartRow To LastRow
TextArray(X - StartRow) = "@" & .Cells(X, NamesColumn)
ListBox1.AddItem .Cells(X, NamesColumn)
Next
End With
End Sub

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

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
End Sub

Private Sub TextBox1_Change()
Dim X As Long
Dim ListHeight As Long
Dim UboundPersons As Long
Dim Individual As String
Dim Persons() As String
UboundPersons = FilterB01(TextArray, "@" & TextBox1.Text, _
Persons, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UboundPersons -1 Then
With ListBox1
.Clear
For X = 0 To UboundPersons
.AddItem Mid$(Persons(X), 2)
Next
End With
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
Else
ListBox1.Clear
For X = 0 To UBound(TextArray)
ListBox1.AddItem Mid$(TextArray(X), 2)
Next
End If
End Sub

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

Private Function FilterB01(SourceArray() As String, _
Match As String, _
TargetArray() As String, _
Optional Include As Boolean = True, _
Optional Compare As VbCompareMethod = vbBinaryCompare) As Long

' by Donald, , 20000918
' returns Ubound(TargetArray), or -1 if
' TargetArray is not bound (empty array)
' Code obtained at: http://www.xbeat.net/vbspeed/c_Filter.htm

Dim i As Long

' make maximal space
ReDim TargetArray(UBound(SourceArray) - LBound(SourceArray))

FilterB01 = -1

For i = LBound(SourceArray) To UBound(SourceArray)
If Len(SourceArray(i)) Then
If Include = CBool(InStr(1, SourceArray(i), Match, Compare)) Then
FilterB01 = FilterB01 + 1
TargetArray(FilterB01) = SourceArray(i)
End If
Else
' we want a match if Source and Match are both ""
' but InStr does not work on zero-length strings, so:
If Include = Not CBool(Len(Match)) Then
FilterB01 = FilterB01 + 1
' is "" anyway, so we spare this line:
''TargetArray(FilterB01) = SourceArray(i)
End If
End If
Next

' erase or shrink
If FilterB01 = -1 Then
Erase TargetArray
Else
ReDim Preserve TargetArray(FilterB01)
End If

End Function

Selection based on partial input
 

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