• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA combo box to search : sorting needed

HI chandoo
I have attached an extract of my file.
I've created a Form called 'Orders' and at the bottom a search Combo box ( with three options). Depending which option you choose the .list is loaded onto the control
My problem is actually sorting the list before is loaded onto the Combo box. The first Option by 'Order Number' ( is already sorted as per the table when the form is initialised, so no problem there), but the other 2 Options (by 'Policy Number' or By 'Last name') need to be sorted beforhand.
An lastly the last Option By 'last Name' I would like to add the 'Order number' corresponding to the Customer so i can select the correct one, since one customer can have more than one (1) order number.
Hope i am clear.
Can you help , please? Thanks
p.s: Below the code for the search By 'Last Name', ( as you can see with my solution indented which does not work).

Code:
Private Sub OptionButton_LastName_Click()
Me.CmbSearchBy = ""
lr = Sheets("Orders").Cells(Rows.Count, 7).End(xlUp).Row
Me.CmbSearchBy.RowSource = "Orders!G8:G" & lr
'Me.CmbSearchBy.ColumnCount = 7
'Me.CmbSearchBy.ColumnWidths = "30,20,10,10,20,20,20"
'Me.CmbSearchBy.BoundColumn = 1
End Sub
Many Thanks
Martin
 

Attachments

Marc L

Excel Ninja
Hi, according to your attachment the mod to apply to the Form_Orders module :​
Code:
Private Sub SearchBy(C%)
         Application.EnableEvents = False
         CmbSearchBy.Text = ""
    With Sheet3.ListObjects(1)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add .ListColumns(C).Range, 0, 1
         If C = 6 Then .Sort.SortFields.Add .ListColumns(1).Range, 0, 1
        .Sort.Header = 1
        .Sort.Apply
         If C = 6 Then CmbSearchBy.List = Evaluate(.DataBodyRange.Columns(6).Address & "&"" #""&" & .DataBodyRange.Columns(1).Address) _
                  Else CmbSearchBy.List = .DataBodyRange.Columns(C).Value2
    End With
         Application.EnableEvents = True
End Sub

Private Sub OptionButton_LastName_Click()
    SearchBy 6
End Sub

Private Sub OptionButton_OrderNo_Click()
    SearchBy 1
End Sub

Private Sub OptionButton_PolicyNo_Click()
    SearchBy 4
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thanks a lot Marc L.
Your solution looks excellent. I will need a bit of time to study , as your solution is very proficient and my level of VBA understanding is basic.
I will review and revert. Thanks a lot !!
 
Hi Marc L.
I have tried your solution and it works very well and in such a simplified way !.
One caveat though is the fact that is NOT loading back the values automatically back onto the Controls ( for the selected choice).
What is your suggestion , as I am stuck on this last bit. ?
Thanks again
Kind regards
Martin
 

Marc L

Excel Ninja
The GO button mod :​
Code:
Private Sub CmdBSearch_Click()
    Dim V, C%, W, R
        V = CmbSearchBy.Text
    Select Case True
           Case OptionButton_OrderNo.Value:     C = 1
           Case OptionButton_PolicyNo.Value:    C = 4
           Case OptionButton_LastName.Value:    W = Split(V, "#"):  If UBound(W) = 1 Then V = W(1): C = 1
    End Select
        If C = 0 Or V = "" Then Beep: Exit Sub Else If IsNumeric(V) Then V = Val(V)
    With Sheet3.ListObjects(1).DataBodyRange
        R = Application.Match(V, .Columns(C), 0):  If IsError(R) Then MsgBox "Order not found", 64, "Search By": Exit Sub
        W = Split(" TxtBox_OrderNo TxtBox_OrderDate TxtBox_Period TxtBox_PolicyNumber CmbBox_CustomerNo" & _
                  " TxtBox_CustomerLastName TxtBox_CustomerFirstName TxtBox_Park CmbBox_PropertyType" & _
                  " TxtBox_PropertyName TxtBox_SectorLot TxtBox_ParcelTotals TxtBox_BurialSites TxtBox_Cash" & _
                  " TxtBox_Premium TxtBox_PayOut CmbBox_TeamNumber TxtBox_Comm_Boss TxtBox_Comm_Agent")
        For C = 1 To UBound(W):  Controls(W(C)).Text = .Cells(R, C).Text:  Next
    End With
End Sub
You should Like it !​
 

Marc L

Excel Ninja
The GO button variation with the Controls indexes instead of names :​
Code:
Private Sub CmdBSearch_Click()
    Dim V, C%, W, R
        V = CmbSearchBy.Text
    Select Case True
           Case OptionButton_OrderNo.Value:     C = 1
           Case OptionButton_PolicyNo.Value:    C = 4
           Case OptionButton_LastName.Value:    W = Split(V, "#"):  If UBound(W) = 1 Then V = W(1): C = 1
    End Select
        If C = 0 Or V = "" Then Beep: Exit Sub Else If IsNumeric(V) Then V = Val(V)
    With Sheet3.ListObjects(1).DataBodyRange
        R = Application.Match(V, .Columns(C), 0):  If IsError(R) Then MsgBox "Order not found", 64, "Search By": Exit Sub
        W = [{1,45,8,10,40,24,23,6,4,16,46,18,21,22,30,31,34,32,33}]
        For C = 1 To UBound(W):  Controls(W(C)).Text = .Cells(R, C).Text:  Next
    End With
End Sub
You may Like it !​
 
Wow Marc !
Is extremely impressive . I must confess my level of 'amateur' programming does NOT allow me to fully understand your code.... But it works 100% correct. Is fantastic. Thanks Very Much
By the way are you based in south Africa?
 
Top