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

cht13er
Sat Mar 15 07:34:12 CDT 2008

On Mar 15, 6:46 am, affordsol <afford...@discussions.microsoft.com>
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

cht13er
Sat Mar 15 07:37:15 CDT 2008

On Mar 15, 8:34 am, cht13er <cht1...@gmail.com> wrote:
> On Mar 15, 6:46 am, affordsol <afford...@discussions.microsoft.com>
> 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

Rick
Sat Mar 15 14:02:11 CDT 2008

> 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

affordsol
Sun Mar 16 02:16:00 CDT 2008

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
>
>

Rick
Sun Mar 16 02:57:35 CDT 2008

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" <affordsol@discussions.microsoft.com> wrote in message
news:3AD88696-B12D-49D0-B5F4-7D7877A660A0@microsoft.com...
> 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
>>
>>

Rick
Sun Mar 16 03:21:02 CDT 2008

> 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, donald@xbeat.net, 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

affordsol
Mon Mar 17 02:59:01 CDT 2008

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 are:
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.ListIndex)
.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.ListIndex)
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

Rick
Tue Mar 18 12:25:44 CDT 2008

I'm not sure why you were not able to get the code I posted in my last
message to work for you... I tested it before posting it and it worked fine.
As a matter of fact, I just tested it again and it still works fine here. If
it matters, I am using both XL2003 and XL2007 here (I don't have an XL97, so
I can't test it on that version). I would ask you to try the code again, but
do so on a new worksheet placing the code on a new UserForm, use default
names for all components placed on the UserForm, and simply type your list
of names into Column B starting at Row 2 on Sheet1. Do you still get your
errors when you do that?

By the way, the code you posted does not work correctly on my system. Using
the names (in this order, although I don't think that matters)... Rick,
Betty, Richard, Ricky... if you type "Ri" into the TextBox, no names remain
in the ListBox for selection.

Rick


"affordsol" <affordsol@discussions.microsoft.com> wrote in message
news:20E2FE65-59B0-40C1-973C-5F18D2841396@microsoft.com...
> 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 are:
> 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.ListIndex)
> .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.ListIndex)
> 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
>