Add Quick Access to Excel Custom Lists

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Excel VBA: Add Quick Access to Custom Lists. Add Custom List Access to Right Click Menu

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

Quick Access to Excel Custom Lists. See Also: Adding a Command Button to the Excel Right Click Menu

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.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft