Filter sheet using combobox on userform

[adam]

Thanks for the reply Rory. I did try that change before you. But it gives me global object error message. And because of that I changed the code as you have seen. But still it does not work.

[adam]

Oh I'm sorry the change finally works. It previously didn't work as the column headers had errors. Thanks Rory & Hans for the help. I do really appreciate that.

Meanwhile,
I have set the system date in the format as dd/MM/yyyy.

Having the above as the format; if I write a customer’s birth date with a day less than 12, the text box “txtDateofBirth” shows the numbers in general format even if I have enabled the columns of the sheet “Data” format as short date format.
But if I write a birth date that has the day’s value greater than 12, the textbox shows the birth date as same as that I have written in the data sheet.

In short;
If I write the date as
18/01/1999 the date in the textbox “txtDateofBirth” appears as same
But if I write the date as 09/01/1999 the date in the textbox “txtDateofBirth” appears as 36404

What may be the reason for this?
Any suggestion or help regarding this will be kindly helpful.

[rory]

Is your textbox linked to the cell using the controlsource property? If not, how is it populated?

[adam]

To populate the text box, I'm using the code provided in the sample workbook uploaded at the Post=16710 by Hans.

[HansV]

In the ListBox1_DblClick event procedure, change the line

Code: Select all

  Me.txtDateofBirth = Me.ListBox1.Column(3)

to

Code: Select all

  Me.txtDateofBirth = Format(Me.ListBox1.Column(3), "dd/mm/yyyy")

and in the cmdOK_Click event procedure, change the line

Code: Select all

  Range("DateOfBirth").Cells(r).Value = txtDateofBirth.Value

to

Code: Select all

  Range("DateOfBirth").Cells(r).Value = CDate(txtDateofBirth.Value)

[adam]

Thanks a lot Hans. It works fine & I do appreciate your help.

[adam]

As with the code embedded in the workbook at Post=16710,the code below looks up data ranges from “Sheet1” to fill the text boxes associated with it. The column it looks up in each row is column “B” which contains the Serial No. The data in this sheet starts from Row “Four” meaning the column headers are at Row “Three”.

Code: Select all

Private Sub cmdOK_Click()
  Dim r As Long
  On Error Resume Next
r = Application.WorksheetFunction.Match(CLng(Me.txtSerialNo.Value), _
    Range("Serials"), 0)
  If Err Then
    MsgBox "Serial Number not found", vbExclamation
    Exit Sub
  End If
  On Error GoTo 0
  Range("CustomerIDS").Cells(r).Value = txtCustomerID.Value
  Range("ReceiptNo").Cells(r).Value = txtReceiptNo.Value
End Sub

Moreover, the code below looks up data ranges from “Sheet2” to fill the text boxes associated with it. The column it looks up in each row is column “B” which contains the Serial No. The data in this sheet starts from Row “Five” meaning the column headers are at Row “Four”.

Code: Select all

Private Sub cmdEdit_Click()
  Dim r As Long
  On Error Resume Next
r = Application.WorksheetFunction.Match(CLng(Me.txtSerial.Value), _
    Range("Serial"), 0)
  If Err Then
    MsgBox "Serial Number not found", vbExclamation
    Exit Sub
  End If
  On Error GoTo 0
  Range("Date").Cells(r).Value = CDate(txtDate.Value)
  Range("Description").Cells(r).Value = txtDescription.Value
End Sub

As using more command buttons takes up space from the user form, how can the ranges under two codes be combined to one so that with one command button (OK) the code does the task for ranges from two sheets.

Any suggestion to do so would be kindly appreciated.

Note: the text boxes associated with the two codes gets populated by double click event procedure from two list boxes & this is not simultaneous.

[HansV]

The code uses different text boxes and ranges. Although you could use an input box to ask the user what to do, it seems confusing to me.

[adam]

Since I'm having two individual text boxes where I have to put the serial number I've named them as txtSerialNo & txtSerial as two text boxes cannot be given one name. Text bos txtSerialNo comes from the Sheet1 & the textbox txtSerial comes from the Sheet2.

cant the line

Code: Select all

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

be combined with

Code: Select all

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

so that when the text boxes are filled it looks up the two ranges?

[HansV]

You could put a combo box cmbSelection on the userform and populate it with appropriate choices, e.g. "Sheet1" and "Sheet2".

Then use code like this:

Code: Select all

Private Sub cmdOK_Click()
  Dim r As Long
  Select Case Me.cmbSelection
  Case "Sheet1"
    On Error Resume Next
    r = Application.WorksheetFunction.Match(CLng(Me.txtSerialNo.Value), _
      Range("Serials"), 0)
    If Err Then
      MsgBox "Serial Number not found", vbExclamation
      Exit Sub
    End If
    On Error GoTo 0
    Range("CustomerIDS").Cells(r).Value = txtCustomerID.Value
    Range("ReceiptNo").Cells(r).Value = txtReceiptNo.Value
  Case "Sheet2"
    On Error Resume Next
    r = Application.WorksheetFunction.Match(CLng(Me.txtSerial.Value), _
      Range("Serial"), 0)
    If Err Then
      MsgBox "Serial Number not found", vbExclamation
      Exit Sub
    End If
    On Error GoTo 0
    Range("Date").Cells(r).Value = CDate(txtDate.Value)
    Range("Description").Cells(r).Value = txtDescription.Value
  End Select
End Sub

[adam]

Thanks for the modification Hans. Since this is an extra combo box I’ve hidden it to make it invisible to the user. What I'm trying to get is to change the code so that Sheet1 could be selected from the combo box when the user double clicks the listbox1. Moreover, if the user double clicks the list box2 how could I make both the sheets be selected?

[HansV]

Why would you do that? I'd leave the combo box visible.

[adam]

Because on occasions I have to edit the text boxes under the code Private Sub cmdOK_Click() and textboxes that comes under the code Private Sub cmdEdit_Click() simultaneously.

[HansV]

You're making it far too complicated.

[adam]

So Ill take what you have mentioned in Post=19110

[adam]

The workbook posted in here searches the row to be edited depending upon the customer ID. Because of this it does not allow the user to edit the customer ID. For this reason I have added a new textbox (txtNewID) to the user form, which will be hidden when the user initializes the user form.

I’ve also added an extra command button Edit Customer ID. When the user clicks this button, the code hides the text box txtCustomerID and visualizes the textbox txtNewID where the user can enter a new ID for the customer.

Unlike the rest of the text boxes in the user form once the customer ID is changed it gets changed from all the locations of the active workbook.

What I’m trying to get help is how to assign the code under the OK button so that when the user clicks the Ok button the customer ID gets edited from any location that exist within the workbook.

Code: Select all

Private Sub cmdOK_Click()
    ChgInfo
End Sub
Private Sub ChgInfo()
    Dim WS As Worksheet
     
    For Each WS In Worksheets
        WS.Cells.Replace What:=txtCustomerID, Replacement:=txtNewID, _
        LookAt:=xlWhole, MatchCase:=False
    Next
End Sub

Private Sub UserForm_Initialize()
Me.txtNewID.Visible = False
    txtCustomerID = Selection
End Sub

[HansV]

Is there a problem with the code that you posted? If so, what?

[adam]

Sorry Hans, I got it solved. Thanks for the reply.

[adam]

Yes there is a problem. Suppose If I change the customer ID and click the Ok button it does get changed from every location. But in another instance if I don't edit the Customer ID but edit some other text box and press the OK button, the customer ID gets cleared both from the list box and the worksheet.

What might be the reason for this?

[HansV]

You should probably check whether txtNewID is visible and filled in:

Code: Select all

Private Sub cmdOK_Click()
  If Me.txtNewID.Visible = False Or Me.txtNewID = "" Then
    MsgBox "You can't change the Customer ID", vbExclamation
  Else
    ChgInfo
  End If
End Sub