Link to home
Start Free TrialLog in
Avatar of darkdell
darkdell

asked on

Creating Sequential Letter Generator in vba. AA AB Revisted

On my previous request on this subject I was provided a code solution that produced a string in the form of:

[Function ColXL(ByVal abc As String) As Long
' ** Covert Number to Letter Seq**
 ' * 108 to DD
 '?ColXL("--AA")
 '?ColXL("ZZZZZ")

abc = Trim(Replace(UCase(abc), "-", ""))
    Do While Len(abc)
        ColXL = ColXL * 26 + (Asc(abc) - vbKeyA + 1)
        abc = Mid(abc, 2)
    Loop
End Function]

- - - - A thru ZZZZZ.   It  works great.   Now I need to popluate a listbox or combobox with values the user can select from.

For Example:
User need 3 Seqs ID's generated.  And the last Seq ID was "C"
User inputs "3" in a textbox and the list box would be populated with "D" ,  "E" , " F" to select from.

User inputs "4" and the Last Seq ID was  "AB"
the list box would be populated with "AC", "AD", "AE", "AF"

User inputs "2" and the Last Seq ID was  "AAAA"
the list box would be populated with "AAAB", "AAAC" etc.

This would occur all the way up to "ZZZZZ"

What's the best way to do this?  
Array?
Table were I update the recordset (rowsource)?

Arrrgh.

Thanks
Avatar of als315
als315
Flag of Russian Federation image

Test sample (with harfang's functions)
DBlistseq.accdb
Avatar of darkdell
darkdell

ASKER

als315

Thank you
That is very very close! But the listbox is including the orginal letter as well.

Like I stated, I need the "next" values indicated by the Q-ty values, excluding the current letter value.  That's why I'm having trouble (the loops).

So "A" with Q-ty 2 would give me "B","C"  not  "A" & "B"
Can you take a step back and explain the ultimate use for this system.

It seems like it is being used for interaction with Excel.
So perhaps an Excel based solution might be more fitting...?
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was going to

Me.MyListBox.RemoveItem 0

at the end of the Sub fill_list code but your solution is much better thank you