Filter sheet using combobox on userform

[HansV]

In that case, you should use

If TogEdit.Value = False Then
...
Else
...
End If

as indicated by me in Post=16799 less than an hour ago.

[adam]

Did you meant to modify the code as below

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  If TogEdit.Value = False Then
  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)
  Else
  Worksheets("Bills").Range("D3") = Me.ListBox1
  End If
End Sub

[HansV]

In your previous version, you wrote the value to cell D3 if the toggle button was off (False), now you do so if it's on (True). Was that your intention?

[adam]

In your previous version, you wrote the value to cell D3 if the toggle button was off (False), now you do so if it's on (True). Was that your intention?

Is this the quote that you are referring?

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”.
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.

Please let me know your response.

[HansV]

Yes...

[adam]

Ok Here comes the details

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”.

By the above quote what I meant was the modification of the code; so that if a user double clicks the list box without touching the toggle button, the "Customer ID" of the highlighted row to be copied to the cell "D3" of the worksheet "Bills".

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.

In the above quote what I meant was that if the user clicks the toggle button and then double click a row; the highlighted row to be copied to the appropriate text boxes in the form. But not the cell "D3".

I hope this details my question clearing it from doubts. Also I hope that I have mentioned my intention over here.

Meanwhile please let me know if your doubt still exists.

[HansV]

Does the code that you posted in Post=16813 do what you want? If so, I'm satisfied.

[adam]

I guess the following code does what I'm in need of

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

But the following code does not

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  If TogEdit.Value = False Then
  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)
  Else
  Worksheets("Bills").Range("D3") = Me.ListBox1
  End If
End Sub

I've attached the workbook for your reference.
Please let me know your reference after this.

[HansV]

If the first does what you want, why change it?

[adam]

Depending upon the following quotes

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?

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.

In that case, you should use

If TogEdit.Value = False Then
...
Else
...
End If

[HansV]

I think we should end this discussion now.

[adam]

Sure why not. My pleasure.

[adam]

When I write the customer ID with a starting alphabetical letter such as C 1234 0r C 1254 into the data sheet and try to edit a record I get the message box "Customer ID not found". But if I write a customer ID as in previous versions 1234 The record is editable. What may be the reason for this?

Any suggestion would be kindly appreciated.

[HansV]

Because in the previous version, the Customer ID was a number. The instruction

Code: Select all

  r = Application.WorksheetFunction.Match(CLng(Me.txtCustomerID.Value), _
    Range("CustomerID"), 0)

tries to match the numeric value of the text entered by the user.

[adam]

How should I change it if I want to write the customer ID as C 1234 etc?
I did try Me.txtCustomerID.Text. But that does not make any sense

[HansV]

The function CLng converts the value entered by the user to a number (Long Integer). Remove this function from the instruction.

[adam]

If you don't mind me asking, I did try as following. But I guess what I had done isn't appropriate and I couldn't get what I want with it.

Code: Select all

  Me.txtCustomerID.Value = Range("CustomerID", 0)

I would be happy if you could correct me.
Thanks in advance.

[HansV]

What does that have to do with the previous question?

[adam]

How should I change it if I want to write the customer ID as C 1234 etc?
I did try Me.txtCustomerID.Text. But that does not make any sense

The function CLng converts the value entered by the user to a number (Long Integer). Remove this function from the instruction.

If you don't mind me asking, I did try as following. But I guess what I had done isn't appropriate and I couldn't get what I want with it.

Code: Select all

  Me.txtCustomerID.Value = Range("CustomerID", 0)

I would be happy if you could correct me.
Thanks in advance.

What does that have to do with the previous question?

Based upon my first question you gave me a suggestion. And depending upon that I made a change to the line of the code mentioned in your reply. But the change I made does not seem to work. Because of this I asked for help once again. I hope I have detailed my question

[rory]

The line Hans told you to remove the CLng from was this one:

Code: Select all

r = Application.WorksheetFunction.Match(CLng(Me.txtCustomerID.Value), _
    Range("CustomerID"), 0)

which would then become:

Code: Select all

r = Application.WorksheetFunction.Match(Me.txtCustomerID.Value, _
    Range("CustomerID"), 0)

As you can see, nothing to do with the line you just posted! ;)