Populating the listbox on a user form

  • I've worked with VBA and programming in Excel for a while, but this is my first try at using a user form that pulls data from the workbook, and I'm completly confused.

    I have a workbook with 52 worksheets. I'm trying to create a user form that lists each worksheet and the value of cell b5 from each of those forms. The user would be able to select only one of the list items (sheet name, and cell value) and click a command button to process the rest of the code.

    Basically I need the form to list like this:
    Week 1 - 1/3/04
    Week 2 - 1/10/04
    Where Week 1 is the sheet name, and the date is the value of cell b5 on that sheet.

    I'm stumped. I've found code on how to get the sheet names, and I know how to create a list of the values of cell B5, but I can't seem to find a way to combine the two in a listbox on a user form.

    Any help would be greatly appreciated!


  • Ok

    To do this, I suggest the following code:

    for each ws in Worksheets

    ComboBox.AddItem(ws.Name & Worksheets(ws.Name).Range("B5").Value)

    next ws

    This will populate your combobox with what you are looking for.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!