Automated alphabetical drop-down list in VBA

[Darmlucht]

Hello all,

This is one of my first posts, so please let me know if I'm not doing it correctly!
I read the forum manual but still, I am human and I make mistakes..
My previous post was approved so I hope this one will be as well.

So here it goes..

I have a userform which can copy specified columns of a specified workbook, to specified columns in the "active" sheet.
You can also choose to copy the whole sheet from one to the other.
It is used in a sort of inventory/stock managing system.
Now, in some columns there are duplicate values, which I don't need and would like to delete.
But I don't need to delete all duplicate values over the whole Sheet. So, some columns should still have duplicates.
My question is, is there a way to make a drop-down list on a userform, that has all the possible column letters in it, by itself?
So without making an extra sheet with a column that has all the letters and then let that populate the drop-down list.
So that you can specify in which column the duplicates should be deleted.

Is there VBA code that I can put in my UserForm that will auto-populate the drop-down list with all the column letters?
There, that is my question in short.

I know I can use an Inputbox and then let the user fill in the letter of the column. I was just wondering if such a thing is possible.
Because, if I let the user fill it in, I need to let it be checked for errors.

-Using Office 2010

Hope this is clear? Or do you need more information?
I don't think it's necessary to add my code/snippets?
If so, let me know and I'll post it!
Sorry if my English is not 100%, not my mother tongue!

Kind regards, and thanks in advance for any help/comment!

-Darm

 

[Fluff]

If your data never goes beyond col Z you can use this

Private Sub UserForm_initialize()
   Dim lc As Long, i As Long
   Dim lst As String
   lc = Cells(4, Columns.Count).End(xlToLeft).Column
   lst = "A"
   For i = 2 To lc
      lst = lst & "," & Chr(i + 64)
   Next i
   Me.ComboBox1.List = Split(lst, ",")
End Sub

 

[MickG]

Try this:-
This code should cover you Usedrange columns count

Private Sub UserForm_Initialize()
Dim a As Long, b As Long, c As Long
Dim lc As String, lb As String, Num As Long
For c = 0 To 24
    For b = 0 To 26
        For a = 1 To 26
            Num = Num + 1
            If Num > ActiveSheet.UsedRange.Columns.Count Then Exit Sub
            If c = 0 Then lc = "" Else lc = Chr(64 + c)
            If b = 0 Then lb = "" Else lb = Chr(64 + b)
                ComboBox1.AddItem Trim(lc & lb & Chr(64 + a))
        Next a
    Next b
Next c

 

[Darmlucht]

@Fluff and @MickG
I will try this first thing tomorrow when I go back to work!
Thanks in advance, from what I can see (and run this VBA in my head), this indeed should do what I want.
I'm not sure, but I don't think the data will exceed column Z, but I will try both solutions and see which one fits best.
Thanks thanks thanks!
I will post my findings and results!

greetings,
Darm

 

[Darmlucht]

Both of the solutions provided by these MrExcel MVP's are working!
And doing exactly what they said, and what I need.
I'll be using the answer given by MickG, but only for the sake of being prepared if the columns go over "Z"

Thank you both for the fast answer and the useable solutions!

King regards,
Darm

 

[MickG]

You're welcome