Filter sheet using combobox on userform

[adam]

I have put up a combo box with the name "cboSearchBy" in the form "SearchPreviousMemos". The purpose of this is to give user options to search by the values given in the combobox.

Code: Select all

Private Sub UserForm_Initialize()
cboSearchby.List = Array("Serial", "Customer ID,"Bill Number")
End Sub

Suppose if the user selects either Serial or Customer ID or any other text mentioned in the cbobox and write the text in the textbox 01, the userform filters or higlights the texts depending on that column.

how may this be done in excel userform?

[HansV]

I have no idea what you're referring to. Is this about exactly the same workbook as the previous posts in this thread?

[adam]

Please find the workbook attached with details

[HansV]

I have moved this string of posts to a separate thread since it doesn't have anything to do with the thread in which you posted it.

You can determine the column in which to search as follows:

Code: Select all

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
  Dim c 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
  ListBox1.ListIndex = -1
  If TextBox1.Text = "" Then  'nothing typed
    Exit Sub
  End If
  Select Case Me.cboSearchBy
    Case ""
      ' Nothing selected
      Exit Sub
    Case "Serial"
      c = 0
    Case "Customer ID"
      c = 1
    Case "Bill Number"
      c = 3
  End Select
  For i = 0 To ListBox1.ListCount - 1
    'use the LIKE operator to compare
    'convert both to Uppercase as well so case does not matter
    If UCase(ListBox1.List(i, c)) Like UCase(s & "*") Then
      ListBox1.ListIndex = i
      Exit Sub
    End If
  Next i
End Sub

You may want to clear the text box when the user selects another item in the combo box:

Code: Select all

Private Sub cboSearchBy_Change()
  Me.TextBox1 = ""
End Sub

[adam]

Thankyou Hans. Your modification worked like a piece of magic & I do really appreciate it.

[adam]

The userform named in the attached work book has text box which needs to be filled by appropriate information when the user double clicks the listbox. But there's a problem in loading the form with the macro button.

I've tried to figure out the problem a couple of time. But am failing to succeed.

Would be happy if any help is being provided.

[adam]

the ,macro code to load the userform in the workbook uploaded is as

Sub Dialog()
frmEditCustomer.Show
End Sub

but even if the code is changed to

Sub Dialog()
frmEditCustomerRecord.Show
End Sub

The form does not show

[HansV]

You must, of course, use

frmEditCustomerRecord.Show

for there is no userform named frmEditCustomer.

But the code then fails on the line

txtCustomerID.Text = Range("CustomerID")(txtRow.Text).Value

because the workbook doesn't contain a defined name CustomerID. Nor does it contain ANY of the other defined names referred to in UpdateData.
The names that have been defined are ALL invalid.
So you will have to delete the existing names (click Name Manager in the Formulas tab of the ribbon) and define each of the names that your code refers to.

[adam]

Thanks for letting me know the problem.

Code: Select all

=OFFSETData!,1,MATCH("CustomerIDData!,0)-1,COUNTA(Data!)-1,1)

Should I define the names for each text as above. For changing each text. Such as Customer ID , Address & so on

[adam]

Sorry, but as follows

Code: Select all

=OFFSET(Data!,1,MATCH("CustomerID",Data!,0)-1,COUNTA(Data!)-1,1)

[HansV]

Data! with nothing after it is meaningless; you must provide a cell address, e.g.

=OFFSET(Data!$A$1,1,MATCH("Customer ID",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)

(Note that the text in the sheet is Customer ID with a space between Customer and ID, not CustomerID)

[adam]

I've defined the names in the formula as follows

Code: Select all

=OFFSET(Data!$A$1,1,MATCH("Customer ID",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Name",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Address",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Age",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Sex",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Date of Birth",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)
=OFFSET(Data!$A$1,1,MATCH("Comments",Data!$1:$1,0)-1,COUNTA(Data!$A:$A)-1,1)

and change the UpdateData event as follows

Code: Select all

Private Sub UpdateData()
'get fresh values for all textboxes from the spreadsheet
txtCustomerID.Text = Range("CustomerID")(txtRow.Text).Value
txtName.Text = Range("Name")(txtRow.Text).Value
txtAddress.Text = Range("Address")(txtRow.Text).Value
txtDateofBirth.Text = Range("DateofBirth")(txtRow.Text).Value
txtAge.Text = Range("Age")(txtRow.Text).Value
cboSex.List = Range("Sex")(txtRow.Text).Value
cboComments.List = Range("Comments")(txtRow.Text).Value
End Sub

Note: I've changed the combo box's text instead of .Text to .List
But still the userform does not seem to load up.

any solution please??!!

[HansV]

Please post the updated workbook.

[adam]

Attached, please find the Updated workbook

[HansV]

One problem is that cell F1 contains "Sex " (with a space after the word) instead of "Sex".

Another problem is that when you load the userform, and immediately call UpdataData, txtRow is still empty, so it doesn't provide a valid row number. To get around this, add the line

Me.txtRow = 1

at the beginning of Userform_Initialize.

And in UpdateData, you try to set the List of cboSex and cboComments. This is not correct, you set their List property in Userform_Initialize. In UpdateData, you must set the Value of these combo boxes.

[adam]

Thank You Hans I've made changes according to you and the form seems to load up.
By the way when the userform is loaded up the text boxes in the userform gets filled with the values of the first record from the sheet "Data". Instead of this method How could I create a double click event procedure so that when the user double clicks a row from the listbox the appropriate values gets filled in the text boxes.

I did try the following. But as you might point out it was pointless.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 UserForm("EditCustomerRecord").Me("txtCustomerID") = Me.ListBox1
UserForm("Name").Me("txtAddress") = Me.ListBox1
End Sub

[HansV]

You need to refer to the different columns of the list box. Here is the first part, I'll leave it to you to finish it by filling in ...:

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Me.txtCustomerID = Me.ListBox1.Column(0)
  Me.txtName = Me.ListBox1.Column(1)
  ...
End Sub

[adam]

In general what I’m trying to work out is when the userform is loaded all the text boxes and combo boxes to be empty. (the code doesn't do this)

Next, when I select a search category from the combo box and type the search phrase the text box; row containing the search phrase to be highlighted from the listbox. (the code already does this part)

When the highlighted row is double clicked the highlighted row in the list box should get copied into the corresponding text boxes and combo boxes of the userform. (the code does this part)

And finally after editing the values in the text boxes & comboboxes and press ok, the texts in the text boxes of the form to be copied to the corresponding columns and rows of the worksheet. (the code does not seem to do this)

I want the command buttons “cmdPrevious” ,“ cmdNext” & text box “txtRow” along with their associated codes to be removed from the form. But still keep the form working.
Only the buttons OK and cancel to exist in the userform.
Here’s my final modification of the code. But it does not seem to work as I have mentioned.

Code: Select all

Option Explicit

Private Sub cboSearchBy_Change()
  Me.txtEnterParameter = ""
End Sub
Private Sub cmdCancel_Click()
    Unload Me
End Sub


Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
End Sub

Private Sub txtEnterParameter_Change()
  'the change event runs each time the user
  'types into a text box
  Dim s As String
  Dim i As Integer
  Dim c As Integer
  s = txtEnterParameter.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
  ListBox1.ListIndex = -1
  If txtEnterParameter.Text = "" Then  'nothing typed
    Exit Sub
  End If
  Select Case Me.CboSearchBy
    Case ""
      ' Nothing selected
      Exit Sub
    Case "Customer ID"
      c = 0
    Case "Name"
      c = 1
    Case "Address"
      c = 2
  End Select
  For i = 0 To ListBox1.ListCount - 1
    'use the LIKE operator to compare
    'convert both to Uppercase as well so case does not matter
    If UCase(ListBox1.List(i, c)) Like UCase(s & "*") Then
      ListBox1.ListIndex = i
      Exit Sub
    End If
  Next i
End Sub

Private Sub UserForm_Initialize()
Me.txtRow = 1
UpdateData
CboSearchBy.List = Array("Customer ID", "Name", "Address")
cboSex.List = Array("Male", "Female")
cboComments.List = Array("New", "Old")
End Sub

Private Sub txtDateofBirth_AfterUpdate()
  Dim d1 As Date
  Dim d2 As Date
  Dim Age As Integer
  If Not IsDate(Me.txtDateofBirth.Value) Then
    MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
    Me.txtAge = ""
  Else
    d1 = CDate(Me.txtDateofBirth)
    d2 = Date
    Age = Year(d2) - Year(d1)
    If Month(d2) < Month(d1) Or (Month(d2) = Month(d1) And Day(d2) < Day(d1)) Then
      Age = Age - 1
    End If
    Me.txtAge = Age
  End If
  'store the value of the textbox in the spreadsheet
Range("DateofBirth")(txtRow.Text).Value = txtDateofBirth.Value
End Sub

Private Sub txtCustomerID_AfterUpdate()
'store the value of the textbox in the spreadsheet
Range("CustomerID")(txtRow.Text).Value = txtCustomerID.Value
End Sub
Private Sub txtName_AfterUpdate()
'store the value of the textbox in the spreadsheet
Range("Name")(txtRow.Text).Value = txtName.Value
End Sub
Private Sub txtAddress_AfterUpdate()
'store the value of the textbox in the spreadsheet
Range("Address")(txtRow.Text).Value = txtAddress.Value
End Sub
Private Sub txtAge_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'store the value of the textbox in the spreadsheet

If Not IsNumeric(txtAge.Text) Then
'only allow numeric values
    Cancel = True
Else
    Range("Age")(txtRow.Text).Value = txtAge.Value
End If
End Sub

Private Sub cboSex_AfterUpdate()
'store the value of the textbox in the spreadsheet
Range("Sex")(txtRow.Text).Value = cboSex.Value
End Sub

Private Sub cboComments_AfterUpdate()
'store the value of the textbox in the spreadsheet
Range("Comments")(txtRow.Text).Value = cboComments.Value
End Sub

Private Sub txtRow_AfterUpdate()
'go to the right row

If txtRow.Text < 1 Then
'can't go above the top of the range
    txtRow.Text = 1
ElseIf txtRow.Text > Range("Data").Rows.Count Then
'can't go more than 1 row below the end of the range
    txtRow.Text = Range("Data").Rows.Count
End If
UpdateData
txtCustomerID.SetFocus
End Sub

Private Sub UpdateData()
'get fresh values for all textboxes from the spreadsheet
txtCustomerID.Text = Range("CustomerID")(txtRow.Text).Value
txtName.Text = Range("Name")(txtRow.Text).Value
txtAddress.Text = Range("Address")(txtRow.Text).Value
txtDateofBirth.Text = Range("DateofBirth")(txtRow.Text).Value
txtAge.Text = Range("Age")(txtRow.Text).Value
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.txtCustomerID = Me.ListBox1.Column(0)
Me.txtName = Me.ListBox1.Column(1)
Me.txtAddress = Me.ListBox1.Column(2)
Me.txtDateofBirth = Me.ListBox1.Column(3)
Me.txtAge = Me.ListBox1.Column(4)
Me.cboSex = Me.ListBox1.Column(5)
Me.cboComments = Me.ListBox1.Column(6)
End Sub

What have I missed here?

[HansV]

First you wanted the text boxes to be filled, now you don't want them to be filled when the form opens, and you want the form to do something completely different than what it was designed for. Can you explain why you've changed your mind so radically?

[adam]

First you wanted the text boxes to be filled, now you don't want them to be filled when the form opens, and you want the form to do something completely different than what it was designed for. Can you explain why you've changed your mind so radically?

Ok ill keep this part of the code as it is.

But as you might when you click either the previous or next button the rows does not get changed.

When I press the OK button how could I make the changes values in the text box to get copied into the appropriate rows. Meaning the edited values of customer details to get copied into the appropriate rows.