QN: Userform Name Search

[Michael Vaughan]

Hello Everyone,

Can somebody help me with this code that I messed up??? What I am trying to
do is, I created a UserForm that has a TextBox and a SpinButton. I have a
sheet with a database in it that lists a club with Members Names, Addresses,
and Phone No's. Now, the SpinButton portion of the code works, but what I
want to do with the TextBox is, I want to be able to type in a few letters
of the last name, and then it comes up with the name/address and so on info
below where it shows it in the spinbutton. Here is the code that I have
now, can somebody fix the TextBox function for me so that I can type in a
partial name and have it displayed??? NOTE: The code for the TextBox
Change was originally setup for a ListBox, I have a spinbutton instead of a
listbox.

Dim HelpTopic As Integer

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub TextBox1_Change()
'the change event runs each time the user
'types into a text box
Dim s As String
Dim i As Integer
s = TextBox1.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
LabelName.Caption = -1
If TextBox1.Text = "" Then 'nothing typed
Exit Sub
End If
For i = 0 To LabelName.Caption - 1
'use the LIKE operator to compare
'convert both to Uppercase as well so case does not matter
If UCase(LabelName.Caption(i)) Like UCase(s & "*") Then
LabelName.Caption = 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()
HelpTopic = SpinButton1.Value
UpdateForm
End Sub

Private Sub UserForm_Initialize()
' On Error Resume Next
With SpinButton1
.Max =
Application.WorksheetFunction.CountA(Sheets("Members").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm
End Sub


Thanks in adavance.... Michael

 

[Tom Ogilvy]

Use a combobox instead of a textbox. Look at the match related properties.

 

[Michael Vaughan]

Hi Tom,

Not sure what you saying??? Are you saying that I can't use that TextBox
Search function with the Spinbutton??? If so, I would gladly get rid of the
spinbutton. My bottomline goal is, to type in the first 3 letters of the
last name to retrieve the correct information.

Is this possible?? I have a sample from somebody that does it with a list
box, but I was hoping to get the info without the listbox??
mv

 

[Tom Ogilvy]

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

If you set the properties of the combobox not to show the dropdown arrow,
then it will look just like a textbox. The list of the combobox is used to
identify the acceptable values. For best results, the list would need to be
sorted.

 

[Michael Vaughan]

Hi Tom,

OK.. but will I be able to type in the letters into the comboBox like a
TextBox for it to do the search??? I thought the ComboBox didn't allow text
entry. I will try it and see???

 

[Michael Vaughan]

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("Members").Range("A:A"))
.Min = 1
.Value = 1
End With
UpdateForm
End Sub

 

[Tom Ogilvy]

You don't need your change event code. Just set the properties

MatchEntryRequired: set to True
Specifies whether a value entered in the text portion of a ComboBox must
match an entry in the existing list portion of the control. The user can
enter non-matching values, but may not leave the control until a matching
value is entered.

MatchEntry: Look at options but sounds like you want FmMatchEntryComplete
Returns or sets a value indicating how a ListBox or ComboBox searches its
list as the user types.

Style Property: set to fmStyleDropDownCombo
For ComboBox, specifies how the user can choose or set the control's value.


ShowDropButtonWhen: ShowDropButtonWhen (never show the drop button)
Specifies when to show the drop-down button for a ComboBox or TextBox.


That should give you the basic functionality you describe. Look at the help
entries on these (and other properties) for full understanding of behavior.