Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
QN: Userform Name Search
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("Membe rs").Range("A:A")) .Min = 1 .Value = 1 End With UpdateForm End Sub Thanks in adavance.... Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Name Search
Use a combobox instead of a textbox. Look at the match related properties.
-- Regards, Tom Ogilvy "Michael Vaughan" wrote in message ... 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("Membe rs").Range("A:A")) .Min = 1 .Value = 1 End With UpdateForm End Sub Thanks in adavance.... Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Name Search
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" wrote in message ... Use a combobox instead of a textbox. Look at the match related properties. -- Regards, Tom Ogilvy "Michael Vaughan" wrote in message ... 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("Membe rs").Range("A:A")) .Min = 1 .Value = 1 End With UpdateForm End Sub Thanks in adavance.... Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Name Search
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" wrote in message ... 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" wrote in message ... Use a combobox instead of a textbox. Look at the match related properties. -- Regards, Tom Ogilvy "Michael Vaughan" wrote in message ... 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("Membe rs").Range("A:A")) .Min = 1 .Value = 1 End With UpdateForm End Sub Thanks in adavance.... Michael |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Name Search
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??? "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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" wrote in message ... Use a combobox instead of a textbox. Look at the match related properties. -- Regards, Tom Ogilvy "Michael Vaughan" wrote in message ... 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("Membe rs").Range("A:A")) .Min = 1 .Value = 1 End With UpdateForm End Sub Thanks in adavance.... Michael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Name Search
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. -- Regards, Tom Ogilvy "Michael Vaughan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Linking userform to userform in Excel 2003 | Excel Programming | |||
List Search Results in a UserForm | Excel Programming | |||
Search or FIND on a userform. | Excel Programming |