Selection based on partial input

[Jack]

I'm developing in Excel 2003

i have a workbook with two worksheets.
Sheet 1 needs to be the data input
Input Account number
Display Account Name

Display list of data from Sheet 2 as Account number is input
- based on up to the first 3 characters entered.

Sheet 2 has four columns of data
A - Account Number (this is a text field)
B - Account Name
C - Group Name
D - Location

------------------------------------------------------------------------
Data Sample

10117 Company 1 Software South
10120 Company 2 Hardware East
10225 Company 3 Software North

As the account number is entered the above list would display starting in
row 10. By the time 102 is typed the list would reduce to just the last item.

A user could select an account from the displayed list using the up / down
arrows or mouse and enter and the input would be completed.

I stuck on getting the list to display as the data is being typed. Any help
will be appreciated

 

[Phillip]

I'm developing in Excel 2003

i have a workbook with two worksheets.
Sheet 1 needs to be the data input
      Input Account number
      Display Account Name

      Display list of data from Sheet 2 as Account number is input
         - based on up to the first 3 characters entered.

Sheet 2 has four columns of data
      A - Account Number  (this is a text field)
      B - Account Name
      C - Group Name
      D - Location

------------------------------------------------------------------------
Data Sample

10117     Company 1     Software      South
10120     Company 2     Hardware      East
10225     Company 3     Software      North

As the account number is entered the above list would display starting in
row 10. By the time 102 is typed the list would reduce to just the last item.

A user could select an account from the displayed list using the up / down
arrows or mouse and enter and the input would be completed.

I stuck on getting the list to display as the data is being typed.  Anyhelp
will be appreciated

I am not sure of exactly what you want to achieve
but here is some code that works that you can
modify as required

Assumptions as follows

Sheet2
contains your sample data starting in cell A1
first column consists of numbers in ascending order

Sheet1

Display the Control ToolBox (right click om menu bar)

From the Control ToolBox draw out a listbox control
covering cells A10 to A14 one cell wide
Excel will automatically name it ListBox1

From the Control ToolBox draw out a textbox control
covering cells C10 to D10
Excel will automatically name it TextBox1


From the Control ToolBox draw out a command button control
covering cells G1:H1
Excel will automatically name it CommandButton1

Display the properties list from the Control Toolbox
and change the caption property of the command button
to Restore list


Right click the Sheet1 Tab and select View Code

copy and Paste the following code


Dim rng As Range
Dim ignore As Boolean

Private Sub CommandButton1_Click()
ignore = True
Me.TextBox1.Text = ""
Set rng = Sheet2.Range("A1", Sheet2.Range("A1").End(xlDown))
Range("A11").Clear
Me.ListBox1.Clear
Me.ListBox1.List = rng.Value
ignore = False
End Sub

Private Sub ListBox1_Click()
ignore = True
Me.TextBox1.Text = ""
Range("A1").Value = Left(Me.ListBox1.Text, 5)
Range("B1").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R3C4,2,FALSE)"
Range("C1").FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet2!R1C1:R3C4,3,FALSE)"
Range("D1").FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R3C4,4,FALSE)"
ignore = False
End Sub

Private Sub TextBox1_Change()
Dim vList As Variant
If ignore Then
ignore = False
Exit Sub
End If
vList = rng.Value
vList = Application.Transpose(vList)
vList = Filter _
(SourceArray:=vList, _
Match:=Me.TextBox1.Text, _
Include:=True)
ListBox1.List = vList
End Sub


Make sure that the design mode of the control toolbox
is turned off before clicking the command button

When you click the command button the list box will
fill with the account numbers
In the textbox start typing the first 3 digits.
The list will automatically filter and you can
the click the account number in the list you want
It will appear in cell A1 with the other cells
B1 C1 and D1 completed


As I mentioned you will probably need to modify the
code etc to suit your exact requiremnets