Filter sheet using combobox on userform
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
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.
If TogEdit.Value = False Then
...
Else
...
End If
as indicated by me in Post=16799 less than an hour ago.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
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
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
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?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
Is this the quote that you are referring?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?
Please let me know your response.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.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
Ok Here comes the details
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.
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".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â€.
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".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.
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.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
Does the code that you posted in Post=16813 do what you want? If so, I'm satisfied.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
I guess the following code does what I'm in need of
But the following code does not
I've attached the workbook for your reference.
Please let me know your reference after this.
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
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
Please let me know your reference after this.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
If the first does what you want, why change it?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
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
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
I think we should end this discussion now.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
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.
Any suggestion would be kindly appreciated.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
Because in the previous version, the Customer ID was a number. The instruction
tries to match the numeric value of the text entered by the user.
Code: Select all
r = Application.WorksheetFunction.Match(CLng(Me.txtCustomerID.Value), _
Range("CustomerID"), 0)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
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
I did try Me.txtCustomerID.Text. But that does not make any sense
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
The function CLng converts the value entered by the user to a number (Long Integer). Remove this function from the instruction.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
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.
I would be happy if you could correct me.
Thanks in advance.
Code: Select all
Me.txtCustomerID.Value = Range("CustomerID", 0)
Thanks in advance.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter sheet using combobox on userform
What does that have to do with the previous question?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter sheet using combobox on userform
adam wrote: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 wrote:The function CLng converts the value entered by the user to a number (Long Integer). Remove this function from the instruction.
adam wrote: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.I would be happy if you could correct me.Code: Select all
Me.txtCustomerID.Value = Range("CustomerID", 0)
Thanks in advance.
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 questionHansV wrote:What does that have to do with the previous question?
Best Regards,
Adam
Adam
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Filter sheet using combobox on userform
The line Hans told you to remove the CLng from was this one:
which would then become:
As you can see, nothing to do with the line you just posted! ;)
Code: Select all
r = Application.WorksheetFunction.Match(CLng(Me.txtCustomerID.Value), _
Range("CustomerID"), 0)
Code: Select all
r = Application.WorksheetFunction.Match(Me.txtCustomerID.Value, _
Range("CustomerID"), 0)
Regards,
Rory
Rory