MS-Excel / General Formatting

---

Add a Custom List to the Fill Handle

Once you have created 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 tutorial adds the list to the Fill Handle.

Creating Excel Custom Lists via the Excel Fill Handle is a great way to quickly get a list of numbers or text onto a worksheet. Excel has built-in Custom Lists for Weekdays (Mon-Fri), Months (Jan-Dec) and numeric sequences, but you can also add your own Custom Lists. With this tutorial, you can add your own Custom List to the Fill Handle, to remind you which item must be entered first in a cell.

First, you need to create your list by entering it on a worksheet. Let's say you have 10 names in cells A1:A10 on Sheet 1. Sort the list, if necessary. Then, select the Office button → Excel Options → Popular → Edit Custom Lists (pre-2007, Tools → Options → Custom Lists; on the Mac, Excel → Preferences → Custom Lists). Click the collapse tool to the left of the Import button. Using the mouse pointer, left-click in cell A1 and drag down to A10. Then, click the Import button, followed by OK. From this point on, the custom list will be available to all workbooks on the same computer.

To add the list to your fill handle, right-click on your sheet name and select View Code. Go to Insert → Module and paste the following code:

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, you need to add the following code to the private module of the workbook object (ThisWorkbook):

Private Sub Workbook_SheetBeforeRightClick _
  (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cBut As CommandBarButton
Dim lListCount As Long
Dim lCount As Long
Dim strList As String
Dim MyList
    On Error Resume Next
	With Application

	    lListCount = .CustomListCount
		For lCount = 1 To lListCount
		  MyList = .GetCustomListContents(lCount)
		  strList=.CommandBars("Cell").Controls(MyList(1) & _
			     "..." & MyList(UBound(MyList))).Caption
			.CommandBars("Cell").Controls(strList).Delete
			Set cBut = .CommandBars("Cell").Controls. ?
Add(Temporary:=True)
			    With cBut
				.Caption = MyList(1) & "..." &
                                MyList(UBound(MyList))
				.Style = msoButtonCaption
				.OnAction = "AddFirstList"
			    End With
		Next lCount
	End With
    On Error GoTo 0
End Sub

To get there quickly, while in Excel proper, select Developer → Code → Visual Basic and double-click ThisWorkbook (pre-2007, right-click on the Excel icon, in the upper left next to File, and choose ViewCode). Here's where you need to place the code.

Now, each time you right-click on a cell, you will see the first...last items in each Custom List.

When you click the option the first Custom List item goes into the active cell. Then, you simply drag down via the Excel Fill Handle to get the rest of the list.