Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection based on partial input
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection based on partial input
On 24 Mar, 15:18, Jack wrote:
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 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("A1:D1").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection based on partial input | Excel Programming | |||
sum if based on PARTIAL content of another cell | Excel Worksheet Functions | |||
sumif based on partial match | Excel Worksheet Functions | |||
Partial input | Excel Discussion (Misc queries) | |||
Macro to change list box input range based on selection made in another cell | Excel Programming |