Populate Excel 2010 listbox with column headings

Posted on 2012-08-17
Last Modified: 2012-08-18
I have a database in a worksheet (called “Database”) that runs from cell A1 to ET4160 at the moment (it always expands downward and occasionally rightward). I want to populate a listbox with the database’s column headers, which run from G1 to ET1.  I thought the best way to do this was to collect the header items into an array and populate the listbox from the array, but it’s not working. I get a runtime 13; type mismatch on the ‘For i = 1 To UBound(myVar)‘ statement. Here is the relevant part of the code:

Dim Wks As Excel.Worksheet
Dim myVar As Variant, I as integer

Set Wks = ThisWorkbook.Worksheets("Database")
With Food.LstItem

If WorksheetFunction.CountA(Cells) > 0 Then
'Determine last used column of Database (by searching backwards by columns)
LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
End If

'Convert column number to alpha:
If LastCol > 26 Then
    lastColAlpha = Chr(Int((LastCol - 1) / 26) + 64) & Chr(((LastCol - 1) Mod 26) + 65)
    lastColAlpha = Chr(LastCol + 64)
End If

myVar = Wks.Range("G1:I1").Value
myVar = Application.WorksheetFunction.Transpose(ListItems) 'convert values to vertical array

For i = 1 To UBound(myVar)
      .AddItem myVar(i) 'populate the listbox
      Next i
  .ListIndex = -1 ' means no items selected, set to 0 to select the first item
End With

Open in new window

I know arrays are powerful but my fluency with them is weak - can someone tell me what I'm missing here?
Question by:marrick13
    LVL 35

    Accepted Solution

    After looking at myVar in the watch window I used this code to put all headings in the listbox:
            myVar = Wks.Range("A1:" & lastColAlpha & "1").Value
            For I = 1 To UBound(myVar, 2)
                .AddItem myVar(1, I) 'populate the listbox
            Next I

    Open in new window


    Author Closing Comment

    That does the trick - thank you very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now