Creating Sequential Letter Generator in vba. AA AB Revisted

Posted on 2012-08-24
Last Modified: 2012-08-27
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

abc = Trim(Replace(UCase(abc), "-", ""))
    Do While Len(abc)
        ColXL = ColXL * 26 + (Asc(abc) - vbKeyA + 1)
        abc = Mid(abc, 2)
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?  
Table were I update the recordset (rowsource)?


Question by:darkdell
    LVL 39

    Expert Comment

    Test sample (with harfang's functions)

    Author Comment


    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"
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...?
    LVL 39

    Accepted Solution

    No problems. Change function to:
    Sub fill_list(ByVal Str As String, N As Integer)
    Dim i As Integer
    i = 0
    With Me.MyListBox
      For i = .ListCount - 1 To 0 Step -1
            .RemoveItem i
      Next i
      For i = 0 To N - 1
            Str = XLcL(ColXL(Str) + 1)
            .AddItem Str
      Next i
    End With
    End Sub

    Open in new window

    String  .AddItem Str should be moved down

    Author Comment

    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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now