Automated alphabetical drop-down list in VBA

Darmlucht

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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:
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top