Filter Data In User Form

[HansV]

Does that sound logical to you?

[adam]

I would be happy If I get any help to make it logical If what I had assumed is inlogical

[HansV]

Have you tried changing Range("A4") to Range("A3") ?

[adam]

Yes I did try & I'm still out of luck. If I'm able to do I wouldn't have come to a forum.

[HansV]

Did you replace ALL instances?

[adam]

I guess YES

[HansV]

Could you post the most recent version of your workbook?

[adam]

Attached please find the document of interest.

[HansV]

You have *not* changed Range("A4") where needed.

[adam]

which line is that? If I may ask

[HansV]

Surely you can look for Range("A4") yourself?

[adam]

Oh Hans!

Code: Select all

a = Sheets("Invoice").Range("A4").CurrentRegion.Offset(1, 1).Resize(, 1)

is changed to

Code: Select all

a = Sheets("Invoice").Range("A3").CurrentRegion.Offset(1, 1).Resize(, 1)

there's no improvement
and if the whole code is changed to A4 I cant get what I had asked.

[HansV]

Please read my previous replies more carefully.

[adam]

Hans, as per your replies and suggestion I have changed my mind to leave the code as it is; meaning to use a combo box and a list box to filter the data from the sheet “Orders”

But since my data starts from the row 4 and the column that I want to assign is column 2 which contains the serial number neither the original code nor your version of the code does work with my combo box.

Here is how I changed the list box in your version of the code.
Instead of

Code: Select all

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim w(), i  As Long, c  As Long, n  As Long
  ReDim w(1 To UBound(Data, 1), 1 To UBound(Data, 2))
  For i = 1 To UBound(Data, 1)
    If Data(i, 2) = Me.ListBox2.Value Then
      n = n + 1
      For c = 1 To UBound(Data, 2)
        w(n, c) = Data(i, c)
      Next c
    End If
  Next i
 If n > 0 Then
    With Sheets("Orders").Range("M1") 'adjust the range to paste the filtered data
      .Resize(, UBound(Data, 2)).Value = Sheets("Orders").Range("A4"). _
        Resize(, UBound(Data, 2)).Value
      .Offset(1).Resize(n, UBound(Data, 2)).Value = w
      .Offset(1).Resize(n, UBound(Data, 2)).Name = "SourceRng"
    End With
    With Me.ListBox1
      .ColumnCount = UBound(Data, 2)
      .ColumnHeads = True
      .RowSource = Range("SourceRng").Address(External:=True)
    End With
  End If
End Sub

I tried by using

Code: Select all

Private Sub ComboBox1_Change()
Dim w(), i  As Long, c  As Long, n  As Long
ReDim w(1 To UBound(Data, 1), 1 To UBound(Data, 2))
For i = 1 To UBound(Data, 1)
    If Data(i, 1) = Me.ComboBox1.Value Then
        n = n + 1: For c = 1 To UBound(Data, 2): w(n, c) = Data(i, c): Next
    End If
Next
'# 1
'If n > 0 Then
'    With Me.ListBox1
'        .Clear
'        .ColumnCount = UBound(Data, 2)
'        .List = w
'    End With
'End If
'if you need column headings as well then use # 2
'#2
If n > 0 Then
    With Sheets("Orders").Range("M1") 'adjust the range to paste the filtered data
        .Resize(, UBound(Data, 2)).Value = Sheets("Orders").Range("A4"). _
        Resize(, UBound(Data, 2)).Value
        .Offset(1).Resize(n, UBound(Data, 2)).Value = w
        .Offset(1).Resize(n, UBound(Data, 2)).Name = "SourceRng"
    End With
    With Me.ListBox1
        .ColumnCount = UBound(Data, 2)
        .ColumnHeads = True
        .RowSource = Range("SourceRng").Address(external:=True)
    End With
End If
End Sub

But it didn’t work.
And as in the original code I tried by using
The changes that you have made to the original code is using the line

Code: Select all

 If Data(i, 2) = Me.ListBox2.Value Then

Instead of the line

Code: Select all

  If Data(i, 1) = Me.ComboBox1.Value Then

Also the following

Code: Select all

        n = n + 1: For c = 1 To UBound(Data, 2): w(n, c) = Data(i, c): Next
    End If
Next

Is changed as

Code: Select all

n = n + 1
      For c = 1 To UBound(Data, 2)
        w(n, c) = Data(i, c)
      Next c
    End If
  Next i

How could I make my combo box to work by having the data to start from row 4 and the column that I want to assign is column 2 of the orders sheet?

I hope this is within the limit and any assistance if provided will be kindly appreciated.
In Short: to make the combo box work instead of the list box in the workbook that you had uploaded

[HansV]

You're still asking us to rewrite the code for you.

I gave you a working version in Post=17762 on the first page of this thread. If you're not able to modify it to suit your needs, I suggest that you stick with that version.

[adam]

Thanks for the reply. By the way just want to let you know that I've never asked to rewrite the code.

[StuartR]

Adam,

You seem to have great difficulty taking example code and making changes to it that people suggest.

I think you may find it very useful to take a basic course in VBA programming, to make sure that you have all the basic concepts and help you to benefit from the advice that people give you.

[adam]

Finally I have solved the problem.

But there’s a minor change that I couldn’t over come.
I have added an lsitbox1 one to the user form, which when double clicked fills the text boxes and the combo boxes assigned. When the combo box “Serial No” is filled by double clicking the list box 1 I get the message “Could not get column property. Invalid property array index”
But If I select a number from the combo box the list box 2 gets filled.
I would be happy if I’m told what the reason behind this is.

I've attached the workbook for your reference.

Any help would be kindly appreciated.

By the way, thanks Stuart for letting me know that I have great difficulty taking example code and making changes to it that people suggest.

Also thanks for your opinion that I may find it very useful to take a basic course in VBA programming, to make sure that I have all the basic concepts and help me to benefit from the advice that people give me.

[HansV]

The ListBox1_DblClick event procedure changes cboSerialNo, but this causes the event procedure cboSerialNo_Change to run. This, in turn, writes data to the workbook. Because this might influence the row source of ListBox1, the selection in ListBox1 is canceled.
To get around this, you must place the line

Me.cboSerialNo = Me.ListBox1.Column(0)

at the end of ListBox1_DblClick instead of at the beginning.

[adam]

Thanks for the help Hans. I do really appreciate that.