|
|
|
|
|
|
Excel
|
Listbox filled with matching names in sheet two from partial searchstring in sheet1
|
|
Ease of Use
|
Intermediate
|
Version tested with
|
2002, 2003
|
Submitted by:
|
Charlize
|
Description:
|
Populate a listbox by entering a partial searchvalue in column A on sheet1 after hitting enter. Listbox will show matching names in sheet2.
|
Discussion:
|
Maybe usefull for a helpdesk or Debit - Credit controller. Taking an incoming call and need a record for every call. Type in partial name and you can see all the matching results in the listbox with adress and city (maybe a 4th column to see if he has paid for the helpdesk).
|
Code:
|
instructions for use
|
--- place the text between --- In the code For sheet1
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
UserForm1.Show
End If
End Sub
--- End of place this In sheet1
--- place the text between --- In a module
Option Explicit
Function xlLastRow(Optional WorksheetName As String) As Long
If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If
With Worksheets(WorksheetName)
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function
--- End of place this In a module
--- place the text between --- In the code For a userform
Option Explicit
Option Compare Text
Private Sub UserForm_Activate()
Dim Cell As Range
Dim Row_Counter As Integer
Dim Pos As Integer
Dim MyList() As String
Dim No_Pos As Integer
Dim Current_pos As Integer
Dim Search_name As String
Dim Real_last_row As Integer
Real_last_row = xlLastRow("Sheet2")
Current_pos = ActiveCell.Row
Current_pos = Current_pos - 1
Search_name = Worksheets("Sheet1").Range("A" & Current_pos)
For Each Cell In Worksheets("Sheet2").Range("A2:A" & Real_last_row)
If Cell Like "*" & Search_name & "*" Then
No_Pos = No_Pos + 1
End If
Next Cell
Row_Counter = 2
Pos = 0
ReDim Preserve MyList(No_Pos, 3)
For Each Cell In Worksheets("Sheet2").Range("A2:A" & Real_last_row)
If Cell Like "*" & Search_name & "*" Then
MyList(Pos, 0) = Worksheets("Sheet2").Range("A" & Row_Counter)
MyList(Pos, 1) = Worksheets("Sheet2").Range("B" & Row_Counter)
MyList(Pos, 2) = Worksheets("sheet2").Range("C" & Row_Counter)
Pos = Pos + 1
Row_Counter = Row_Counter + 1
Else
Row_Counter = Row_Counter + 1
End If
Next Cell
Application.ShowToolTips = True
With ListBox1
.ColumnCount = 3
.ColumnWidths = "3 cm;3 cm;3 cm"
.ControlTipText = "Possible matches ..."
.ListStyle = fmListStylePlain
.SpecialEffect = fmSpecialEffectFlat
End With
ListBox1.List = MyList
End Sub
--- End of place this In the userform_activate
|
How to use:
|
- hit alt+f11 for the vbe
- copy each section to the place indicated between --- without those ---
- right mouse click on sheet1, choose 1st item in pop-up menu and past the code that you've copied to the clipboard
- to insert module, right mouse click on project on left side of screen, choose insert module
- to insert code for module, right mouse click on module, choose 1st item in pop-up menu and past the code that you've copied to the clipboard
- if no userform present, insert one - right mouse click, choose insert form
- to insert code for userform - right mouse click on userform, choose 1st item in pop-up menu and past the code that you've copied to the clipboard
- go back to excel and save your workbook
|
Test the code:
|
- add some names in sheet2 - column A = name - column B = adress - column C = city
- in sheet1 type a partial searchstring in column A and hit enter
- a listbox should popup with the result in it
- note : option compare text in userformcode means that "S" and "s" are equal. So if you type in s you'll get Smith, Jones, ... so every name with an s in it.
|
Sample File:
|
VBA-Express - Address Validation-Listbox-v2.zip 17.95KB
|
Approved by mdmackillop
|
This entry has been viewed 425 times.
|
|