Re: Userform Name Search

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 12:50:23 -0400

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. 

-- 
Regards,
Tom Ogilvy
"Michael Vaughan" <michael@mvaughan.net> wrote in message
news:v8CdnZUy388lAqXcRVn-hg@comcast.com...
> 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" <twogilvy@msn.com> wrote in message
> news:eKb1z8ckEHA.3876@TK2MSFTNGP15.phx.gbl...
> > Use a combobox instead of a textbox.   Look at the match related
> properties.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Michael Vaughan" <michael@mvaughan.net> wrote in message
> > news:98qdnQlqNpQjDaXcRVn-pQ@comcast.com...
> > > 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
> > > *** 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
> > >
> > >
> >
> >
>
>