Excel Custom Lists are a great way to quickly get a list of numbers or text onto a Worksheet. These are done via the excel fill handle . Excel has built in Lists for Weekdays (Mon-Fri), Months (Jan-Dec) and numeric sequences. We are also able to add our own Custom Lists via Tools>Options - Custom Lists. However, once you have added a few of your own Custom Lists, it can be hard to remember the first item in the list that must be entered in a cell. This is necessary and then you can drag down via the excel fill handle.
Code For Quick Access to Excel Custom Lists
The code below must be added to the Private Module of the Workbook Object (ThisWorkbook). To get there quickly, while in Excel proper right click on the Excel icon, top left next to File and choose View Code. It is in here the code immediately below must be placed.
Private Sub Workbook_SheetBeforeRightClick _ (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim cBut As CommandBarButton Dim cControl As CommandBarControl Dim lListCount As Long Dim lCount As Long Dim MyList Dim strName As String On Error Resume Next With Application '' Uncomment below to reset back to default. ' .CommandBars("Cell").Reset For Each cControl In .CommandBars("Cell").Controls If cControl.Caption Like "*....*" Then cControl.Delete Next cControl lListCount = .CustomListCount For lCount = 1 To lListCount MyList = .GetCustomListContents(lCount) Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True) With cBut If UBound(MyList) = lListCount Then .Caption = MyList(1) & "...." & MyList(UBound(MyList) - 1) Else .Caption = MyList(1) & "...." & MyList(UBound(MyList)) End If .Style = msoButtonCaption .OnAction = "AddFirstList" End With Next lCount End With On Error GoTo 0 End Sub
Then, add the code below to any standard public Module (Insert>Module)
Sub AddFirstList() Dim strList As String strList = Application.CommandBars.ActionControl.Caption If Not strList Like "*...*" Then Exit Sub ActiveCell = Left(strList, InStr(1, strList, ".", vbTextCompare) - 1) End Sub
Now, each time you right click on a cell you will see the first...last items in each Custom List. Then, when you click it, it places the first Custom List item into the active cell. Then you simply drag down via the excel fill handle to get the rest of the list.
|Automatically Add Date, Time or Date & Time|
|Display Excel AutoFilter Criteria|
|Use AutoFilter in Excel VBA to Filter by Date & Time|
|Formatting Cells Shortcut keys|
|Speed up Excel VBA Macro Code|
|Functions To Determine Excel Calculation Status & Mode|
Free Training Course: Lesson 1 - Excel Fundamentals