Filter sheet using combobox on userform

[HansV]

Your code refers to a named range "Data" which does not exist.

[adam]

How may I overcome this?

[HansV]

You can either replace the name "Data" with an existing name, or define a range "Data".

[adam]

define a range "Data"

Data is the name of the worksheet I'm using. How do I define a range "Data"?

[HansV]

What do you think the range "Data" should refer to?

[adam]

I've defined the name as Data and the formula as =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),7). Thanks for the help Hans. Now when I either click the previous or next button I could move to various records. But suppose I select either Male or female from the combo box and press tab,I'm getting error messages. What might be the reason for this?

[HansV]

Please read the first line of my reply Post=16655 higher up in this thread again.

[adam]

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

Has been replaced after the above reply to

Code: Select all

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

Long time ago....!!!!

But still the error exists.

[HansV]

Have you edited cell F1 to remove the space? When I do that, the combo box on the userform works OK.

[adam]

The cell F1 has text "Sex". I'm unable to figure the space you are assuming? the letters S e x are tight as Sex in cell F1 as long as I could see. And niether a space does exist after them.

[HansV]

There was a space after the "x" in cell F1 the workbook that you attached. This space made the defined name "Sex" invalid. Once I removed the space, the defined name "Sex" became valid, and the combo box worked OK.

[adam]

Thanks Hans. It finally worked. I do really appreciate your help.

[HansV]

By the way, here is a version that doesn't have the Previous and Next buttons, etc.

Edit Customer (3).xlsm

[adam]

Thankyou Hans for the help. This is what I was in need of.

But there's a minute edition which I'm trying to figure out. When I try to change the customer ID it does not get changed.

The scenario is; that the customer ID is written based upon the ID number on a insurance card. Sometimes if the user mistakenly writes the wrong Customer ID it need to be corrected by using this form.

Hence, how could I make the Customer ID editable?

Any suggestions would be kindly appreciated.

[HansV]

The version I posted uses the Customer ID to determine which row should be filled. If you change the Customer ID, either the correct row cannot be found anymore, or the wrong row is updated. So you can't use this userform to edit Customer ID.

[adam]

I’ve put up a toggle button in the userform in order to let the user to expand it.

How may I combine the following code to the already existing double click event code so that if a user double clicks the list box when the toggle button is inactive the column containing the Customer ID to be copied to the cell “D3” of the sheet “Bills”.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Worksheets("Bills").Range("D3") = Me.ListBox1
End Sub

On the other hand if the user double clicks the list box by making the toggle button active. All the values of the double clicked row to be copied into the appropriate text boxes in the userform.

Any suggestion would be kindly appreciated.

[HansV]

You'll have to use an If ... Else ... End If construction that checks the state of the toggle button, and executes

Worksheets("Bills").Range("D3") = Me.ListBox1

if the toggle button is visible, and the original code otherwise.

[adam]

Thankyou Hans. I've modified the code as follow. And I guess it works.

Code: Select all

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)
 If TogEdit.Value = False Then
  Worksheets("Bills").Range("D3") = Me.ListBox1
  End If
End Sub

Meanwhile, just wanted to let you know whether I had missed something

[HansV]

Your version will ALWAYS write the value of the list box to cell D3, whether the toggle button is on or off. Is that what you want?

[adam]

When the toggle button is off I want the values to be written to cell D3 and when the toggle button is on I want the values to be written to text boxes in the form.