How to Add Custom List Using Excel VBA

Home More Excel (VBA) Training Videos

We learnt how to use Excel VBA to perform an Autofill action in our last video.
In our quest for automation in an MS Excel project we may like to create custom lists using Excel VBA. Let's see how we can attach a macro to a command button that creates custom lists with a single click and populates the worksheet with relevant data and helps us avoid data entry errors.
The complete macro code for the command button is given below:
Private Sub CommandButton1_Click()
Application.AddCustomList ListArray:=Array("Tom", "Mary", "Harry", "Williams", "Patricia", "Barbara", "James")
ActiveCell.Value = "Tom"
Selection.AutoFill Destination:=Range("A2:A8")
End Sub
Watch the Excel training video below to see how you can create a custom list with Excel VBA or a macro: