Selecting multiple lines for user form

[Michael Simpson]

Can anyone suggest a good way of solving the following problem (I'm not looking for the code ..... yet )

I have a sheet based on customers and countries (hundreds of them). I need to produce an agenda in Word based on the selected customers. I have all the code in place except the actual customer selection. At one stage, I was thinking of adding a drop down list to the user form with multiple select options, but with hundreds of customers (albeit sorted), that seemed a bit "heavy-handed".

Then I thought of allowing them to start the user form and include code there that asked them whether they'd selected all the relevant customers for the agenda. If not, close the user form and let them select more.

Or..... get them to select the customers, show the user form that includes a drop down list with the currently selected customers already marked (that way, they could select a few extra ones if they'd forgotten them and then create the agenda).

Any suggestions on the best way to go/design the solution ???

 

[Sandeep Warrier]

Hi

If I understood your requirement correctly, have a look at Multiextend Listboxes.

 

[Michael Simpson]

Thank you Sandeep

I realize that multiextend will allow me to select multiple options, but I was thinking of the problem from a user point of view. If I was to create a listbox that contained hundreds of entries that the user can select from, there's a risk that they will be scrolling up and down this listbox choosing items until the cows come home (not to mention the size of the drop down list).

I was thinking that, maybe, it would be easier for them to select/mark items from the sheet and then show the user form whereby those marked rows from the sheet are already selected in the list box.

If you were the user, which method would you prefer ?

 

[Michael Simpson]

In fact....

has anyone ever seen a drop down list with hundreds of selections available in windows ???

One thought I had was to break the drop down list into manageable "bites". For example, allow the user to choose customers starting with (say) A-E, F-J etc etc. Each time they select an option (F-J) I populate a second list with only those customer starting with F-J. (Oh what fun it'll then be keeping track of which customers they've selected belonging to specific first letters)

 

[Michael Simpson]

A follow-on question

I'm going to go with the idea of one drop down list containing A-E, F-J etc and each time the user selects one of these options, a customer drop down list will be populated with the relevant customers (for example, user selects A-E, then customer drop down populated with all customers starting with letters A-E). However .....

I have no problems writing the code that will loop through the rows, extracting the first character of each customer, checking against one of the 5 current characters etc etc, but I imagine this will be far less efficient than doing a FIND. Can anyone tell me how to change the following code (I'm assuming it won't work) so that it looks only in the first character of the customer column (if find can even do this)

 For row = 1 To 6     ' 6 rows corresponds to the A-E, F-J etc etc
    
        For col = 1 To 5    ' Each letter in A-E or F-J etc
            temp = letters(row, col)    ' Extract one of the 5 letters at a time
            ' Here's where I would [B]like [/B]to be able to do a find
            ' I [B]imagine [/B]I need xlPart rather than XlWhole ......
            ' but is this doable at all ???????
            With Sheets(1).Range("B:B")
                Set Rng = .Find(What:=temp, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

 

[Sandeep Warrier]

Hi

I was going to suggest something similar, but segregating information based on categories (if you have any).

In anycase, I think the fastest way to do grouping of A-F etc... would be to use Autofilter (using the Begins With option).

This should give you a start.

Suppose all your data is in column A, starting from A2

Sub Test()
 With Sheets("Sheet1").Range("A2", Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp))
    .Parent.AutoFilterMode = False
    
    .AutoFilter Field:=1, Criteria1:="=a*"
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Offset(1)
    
    .Parent.AutoFilterMode = False
 End With
End Sub