Solved: Listbox probability

[Dave]

I've been kicking around some code to make a selection in a 2nd listbox based on past history of selections in a first listbox. My "real" setup is the first listbox contains various expense accounts and the second listbox contains various vendors. Often it is the same vendor associated with the same account but for some accounts there are many vendors. I'm growing tired of searching the 2nd listbox for a vendor which is quite often the same for each account. Thus my following efforts to add some automation to the vendor listbox selection based on the account selection. So far, the following code selects the most recent vendor in the second listbox (if there has been one) when an account selection is made. This is helpful but I would like to be able to autoselect the most frequently chosen vendor for each account so as to address those accounts with multiple vendors. I haven't come up with an effective strategy for this and ergo this post. In this example, "A" is used for accounts, "B" is used to record the most recent vendor selection and "C" is used for vendors. On Sheet1 there are 2 listboxes (Listbox1 for account; Listbox2 for vendor) and 2 command buttons (commandbutton1 to load listboxes; commandbutton2 to simulate the data entry that would occur after both listbox selections).
To trial enter accounts A1:etc. Enter vendors C1:etc. Select commandbutton1 to load listboxes. Make account selection. Make vendor selection. Select command button2. Continue making account selections...if there has been a previous vendor it will be selected. If no previous vendor then select a vendor and enter with commandbutton2 etc. I will welcome any suggestions for code changes or alternative stratagies. Dave
Here's the code so far:

Private Sub CommandButton1_Click()
'load listboxes
Dim Last As Integer, Last2 As Integer
Dim Cnt As Integer, Cnt2 As Integer
'account listbox
ListBox1.ListStyle = fmListStyleOption
ListBox1.MultiSelect = fmMultiSelectSingle
ListBox1.Clear
Last = Sheets("Sheet1").Range("A65536").End(xlUp).Row
For Cnt = 1 To Last
ListBox1.AddItem Sheets("Sheet1").Range("A" & Cnt).Value
Next Cnt
'vendor listbox
ListBox2.ListStyle = fmListStyleOption
ListBox2.MultiSelect = fmMultiSelectSingle
ListBox2.Clear
Last2 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
For Cnt2 = 1 To Last2
ListBox2.AddItem Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2
End Sub
Private Sub ListBox1_Click()
'Account listbox
'Account list in Sheet1 "A1:Aetc"
'Vendor list in Sheet1 "C1:Cetc"
Dim rngFind As Range
Dim Last2 As Integer
'last vendor row
Last2 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
'find recent vendor if exists
For Cnt = 1 To Last2
Set rngFind = Sheets("Sheet1").Range("B" & ListBox1.ListIndex + 1) _
.Find(Sheets("Sheet1").Cells(Cnt, 3))
If Not rngFind Is Nothing Then
Exit For
End If
Next Cnt

'no selection in listbox2 if no previous
If rngFind Is Nothing Then
Sheets("Sheet1").ListBox2.ListIndex = -1
Exit Sub
End If

'select Vendor in listbox2
Sheets("Sheet1").ListBox2.Selected(Cnt - 1) = True
End Sub
Private Sub CommandButton2_Click()
'Data entry button
'recent vendor selection in Sheet1 "B"
If ListBox2.ListIndex <> -1 Then
Sheets("SheestIndex <> -1 Thent1").Cells(ListBox1.ListIndex + 1, 2) = _
ListBox2.Value
Else
MsgBox "No Vendor selected"
End If
End Sub

[OBP]

Much easier all round in Access.

[Dave]

Thanks OBP but I would like an XL VBA solution. Dave

[OBP]

Dave, don't you need to store the Account as well as the Vendor in the "most recent" column(s), afterall the recent vendor for account 1 is not much use when you want to select a vendor for account 2?
How are you relating the Accounts and Vendors?

[mdmackillop]

Hi Dave,
Can you post a sample workbook?

[Dave]

Here's a sample workbook. OBP... some misunderstanding. The present routine stores only the most recent vendor selected for each account. The account/vendor relationship is based on their past relationships. What I would like to do is determine which account/vendor relationship has been most frequent in the past. Based on this info, vendor selection would then be the most probable account/vendor relationship. Presently, this routine only stores one previous vendor(last) for each account. I would like to store every account/vendor relationship and their frequency for each account and then make the vendor selection based on the highest frequency. It is a bit challenging for only a "convenience" gain but I thought the general utility might be worth the effort. Thanks for the interest. Dave

[OBP]

Dave, I am getting a subscript out of range error from this routine
CommandButton2_Click()The offending line is
Sheets("SheestIndex <> -1 Thent1").Cells(ListBox1.ListIndex + 1, 2) = _
ListBox2.Value
At this part of the line
Thent1").Cells(ListBox1.ListIndex

and a syntax error here
If ListBox2.ListIndex <> -1 Then
Sheets("SheestIndex <> -1 Thent1").Cells(ListBox1.ListIndex + 1, 2) = _
ListBox2.Value
even after corrcting the obvious Sheestindex spelling

[OBP]

Dave, what I would do if I was doing this is to take all the current Accounts and Vendors and put them on sheet2 in columns a & b, d & e etc, then each time an Account/Vendor pairing is chosen I would increment a counter in sheets Column c or f etc depending on which Account was chosen.
This would give you something like
account1 vendor1 = 2 account2 vendor1 = 1
account1 vendor2 = 5 account2 vendor2 = 3
account1 vendor3 = 4 account2 vendor3 = 9

I would then sort each vendor group by count so that you have
account1 vendor2 = 5 account2 vendor3 = 9
account1 vendor3 = 4 account2 vendor2 = 3
account1 vendor1 = 2 account2 vendor1 = 1

Which provides an ordered group for each list box pair based on most usage.
You could have it all in columns a, b & c and sort by vendor and column c as an alternative.
I am not very good with VBA created Excel list boxes, I could probably write it for you with simple fixed list or Combo Boxes though as I would control the data for the boxes rather than trying to control the list in the list's VBA.
Anything like this I normally do in Access.

[Dave]

Sheets("SheestIndex <> -1 Thent1").Cells(ListBox1.ListIndex + 1, 2) = _
ListBox2.Value
..should be
Sheets("Sheet1").Cells(ListBox1.ListIndex + 1, 2) = _
ListBox2.Value
Not sure what happenned there? OBP thanks for the input. I've been messing around with this and I'm fairly close to a solution. Will post an updated wb when I'm a bit closer. Dave

[mdmackillop]

Hi Dave,
Here's my suggested route, except it doesn't quite work. I'm having problem with listbox1 operation. The principle and code is straightforward, so maybe you can see what I'm missing.

[Dave]

Thanks OBP and mdmackillop for your efforts. mdmackillop I'm sure your approach would be error free and more reliable with a bit of tweaking. I came up with a different approach which works but has some critical failings which may not be fixable. In the attached wb, if vendors have similiar names then an erroneous result will occur (ie Bobs and Bobsmart) which isn't that critical as most vendors have unique names. The critical shortcoming is that only 9 occurences of a vendor selection can occur being that there is only 1 array space to contain the frequency as an ascii character (ie. 10 then becomes 1). I don't think there's any easy fix for this. Dave

[mdmackillop]

Hi Dave,
Have you tried using something like Smart Indenter
http://www.oaltd.co.uk/Indenter/Default.htm
Two clicks to indent all your code making it much easier to follow.

[Dave]

Thanks again mdmackillop. I always wanted one but didn't know where to get it. Very helpful. Have a nice day. Dave

[mdmackillop]

Hi Dave,
I think the main problem with m=y code was the Option buttons in a listbox. It just doesn't seem to like these. Is a userform solution acceptable? or can we just place the option buttons on the sheet?

[Dave]

The "real" thing is for a userform. This is just a test. Nice coincidence. I was sort of hoping that the code would end up so that if I had any 2 listboxes, I could use it... and it wouldn't take up to much cellular space, so to speak. I was hoping just to leave an extra column beside the "driver" listbox1 list to store data. You seem to be in for a pound on this and I'm not sure if past history is at all predictive. The stats guys will tell you this. I know with the ebb and flow of commerce that I'm more likely, and occasionally exclusively, more apt to choose a certain vendor for various accounts at different times... hence it would be handy to just move to that selection without having to search the 2nd listbox. The first code I posted returned to the most recently selected. I'm not sure if the stats guys will say that any effort will improve on this? So.. don't knock yourself out but I'm still kicking it around. Dave

[mdmackillop]

It's shouldn't be impossible to head the list with the last selected item, followed by the remainder in order of precedence.

[mdmackillop]

Try this version

[Dave]

mdmackillop apologies for the delayed response. I haven't yet trialled your code but I will soon. Thanks for your efforts. Here's my version which seems to work. I think I overcame the partial match challenge with some code tweaks. I also decided that if a vendor frequency reaches 9, the record would be cleared and by virtue of that vendors firstness to 9, it's record would remain with a "1" entry and thus be selected next for that account. Just need to add some generalizable utility. I thought that making 2 functions would be required. The first for the driver listbox, on the click event, would pass (listbox1name, listbox1rowstart, listbox2name, listbox2rowstart). The outcome would be listbox2 selection. The second function would pass the same variables when data is entered. This would update the vendor record. I'll give your code a trial and report back. Again, thanks for your time and interest. Dave

[Dave]

mdmackillop I really liked your idea of listing all records in descending order of frequency. Your approach uses alot less code and seemed to work quite well. However, as previously mentioned, my goal is too use the smallest amount of cells possible and eventually make the routine generalizable for any 2 listboxes at any location. I also maybe should have mentioned that both account and vendor names can be added or removed at any time. This may throw a wrench into your routine? I elected to modify my previous approach to: based on account selection, re-order the vendor list in descending order of existing record frequency, followed by an alphabetic sort of the remainder of the list. Seems to work quite well if anyone wants to trial it. Next step seems to be making it generalizable. Thanks again for your time and efforts on this post. Dave
ps. note new file name

[mdmackillop]

Dynamic version

[Dave]

Very nice code and again thought provoking. Thanks for that. I appreciate the learnin'. I've never used a combo box but I can see how with some clever coding it is very handy. It also really helps visualize that array solution that I thought would be too confusing to attempt. Any chance that your combobox/array results can be somehow stored in 1 column on wb close? I couldn't come up with a solution and that's why my code went down this path. Here's my generalizable version. Should be good for any 2 listboxes anywhere... haven't thouroughly tested yet and it does still have that arbitrary 9 thing though. I'll test it for "real" to see if I have a useful solution. Dave

[mdmackillop]

Any chance that your combobox/array results can be somehow stored in 1 column on wb close?
Why does the storage space matter? With a bit of work I could probably save it one cell, but space is hardly at a premium.

[Dave]

Apologies for being so hard to get along with. I want to be able to use this routine with existing and future wbs. To keep things simple, instead of adding a new ws,or assigning an undefined area (re. dynamic entry) to an existing ws to store CB data, I believe that this routine would be most useful if all I had to do was ensure that there is 1 column with blank rows beside the "driver" listbox to store records. In general, I also try to use the minimum amount of ws space to achieve any objective. mdmackillop I really appreciate your efforts but "a bit of work" to store your results in 1 column on wbclose, then retrieve them on wbopen, seems like a bit of an understatement. Other than that 9 thing, I believe that I have arrived at a routine that meets my initial objective plus, thanks to your assistance, offers a far better output than I had envisioned. If you want to do "a bit of work", I'm certain that I will appreciate the extra knowlege offerred, but don't give yourself a headache for my convenience. Have a nice day! Dave

[mdmackillop]

Hi Dave,
You're not "hard to get along with" so no problems there; just trying to find the "best" solution.
Personally, I would keep all this data on a hidden worksheet. The userform can be accessed from anywhere when the workbook is open, and a facility to add Personnel and Expense names is easily added.

One thing not apparent is what use you make of this, because there is no apparent output or associated data. While it's reasonable to tackle one task at a time, an overview may suggerst a different/better approach.

[Dave]

My "real" project that I intend to trial this code on uses continuous userforms for data input and output presentation (ie. some userform is shown during the entire wb operation). There are 2 userforms for which this will be handy. The first is for sales entries. It has 2 listboxes. One for products ("driver") and the second for buyers (dependent). The second userform is for expense entries. It also has 2 listboxes... one for expense accounts ("driver") and the second for vendors (dependent). The output is a record that contains the 2 listbox selection names as part of a VBA generated transaction receipt which is saved in a Word document. So I already have the userform(s), listboxes and corresponding lists. The difficulty I was having is that as the number of dependent entries grows larger, it was taking longer and longer to find the correct buyer/vendor. I know that's all of perhaps 2 secs or so but still I thought that there's probably an easier way... so here we are. Hope this is a bit clearer and not offensive in that I hadn't previously provided this info. Dave

[Dave]

Well that was a bust. Of course the "real" set up wasn't as I had recalled... it was some time ago when I coded it. So, there is no simple ws lists of data loaded to the listboxes...don't figure. For both userforms, the lists were either derived from larger lists or being used as a sort key. They could not be re-ordered easily. Back to the drawing/key board. This version requires 3 columns of blank descending space anywhere, and 2 LOADED listboxes anywhere. It just copies the lists to your assigned destination. Seems to work OK but of course I haven't thouroughly tested it (and it's triple the space :mkay ) I'm going to post the updated wb and consider this solved (unless I come up with something easier.) Cheers! Dave