<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Add Quick Access to Excel Custom Lists

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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

Current Special! Complete ExcelExcel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 InstantBuy/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 theExcel 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 CommandBarButtonDim cControl As CommandBarControlDim lListCount As LongDim lCount As LongDim MyListDim 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 0End 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 Handleto get the rest of the list.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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