Userform Name Search
Hi Tom
OK.. I took out the TextBox and input a ComboBox1. Here is the code below,
but it still doesn't work like I want it to. The SpinButton works, but when
I type in "vau" to match my last name, it doesn't hunt for the record and
display it. Here is my code now:
Dim HelpTopic As Integer
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub ComboBox1_Change()
'the change event runs each time the user
'types into a text box
Dim s As String
Dim i As Integer
s = ComboBox1.Text
'Note the use of the ListIndex property of the ListBox
'If the ListIndex is -1 means nothing selected
'If 0 means the first item selected
ComboBox1.ListIndex = -1
If ComboBox1.Text = "" Then 'nothing typed
Exit Sub
End If
For i = 0 To ComboBox1.ListCount - 1
'use the LIKE operator to compare
'convert both to Uppercase as well so case does not matter
If UCase(ComboBox1.List(i)) Like UCase(s & "*") Then
ComboBox1.ListIndex = i
Exit Sub: UpdateForm
End If
Next
End Sub
Private Sub UpdateForm()
HelpTopic = SpinButton1.Value
LabelName.Caption = Sheets("Members").Cells(HelpTopic, 1)
LabelAdd.Caption = Sheets("Members").Cells(HelpTopic, 2)
LabelHome.Caption = Sheets("Members").Cells(HelpTopic, 3)
LabelWork.Caption = Sheets("Members").Cells(HelpTopic, 4)
LabelCell.Caption = Sheets("Members").Cells(HelpTopic, 5)
LabelEmail.Caption = Sheets("Members").Cells(HelpTopic, 6)
Me.Caption = "Sky-Vu Flyers Membership Listing (Pilot " & HelpTopic &
" of " & SpinButton1.Max & ")"
End Sub
Private Sub SpinButton1_Change()
On Error Resume Next
ComboBox1.ListIndex = SpinButton1.Value - 1
UpdateForm
End Sub
Private Sub UserForm_Initialize()
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Membe rs").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm
End Sub
I am saying the functionality you describe is available in a combobox but
not in a textbox (unless you want to write the code to simulate it). The
spinbutton could be designed to work with either.
Private Sub SpinButton1_Change()
combobox1.ListIndex = SpinButton1.Value - 1
End Sub
|