Automated alphabetical drop-down list in VBA

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
15
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,521
Office Version
  1. 365
Platform
  1. Windows
If your data never goes beyond col Z you can use this
Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
This code should cover you Usedrange columns count
Code:
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
 
Last edited:

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
15
@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

New Member
Joined
Nov 23, 2017
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,308
Messages
5,527,930
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top