none
how to freeze header of ListBox in Excel VBA Userform?? RRS feed

  • Question

  • hi 

    is it possible to freeze first Row (Header) in a listBox of VBA Userform like what we do in Excel Worksheet from view tab and freeze button?

    Sunday, November 24, 2013 5:08 AM

Answers

  • I'm sorry, but there is no way to freeze the first row if you're using AddItem to populate the list box. It only works if the RowSource is a cell range.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Nima Iranian Sunday, November 24, 2013 12:47 PM
    Sunday, November 24, 2013 12:13 PM

All replies

  • If the RowSource of the list box is a range on a worksheet, you can set the ColumnHeads property of the list box to True. Excel will then use the row immediately above the RowSource range as fixed header row.

    In other words, you should not include the header row in the RowSource.

    If you populate the listbox using AddItem or by setting its List, setting ColumnHeads to True won't do what you want - it will only display an empty row at the top of the list box.

    In the screenshot above, the RowSource of the list box has been set to A2:A10.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 24, 2013 10:57 AM
  • Mr. Hans,

    with thanks

    yes I am using AddItem  to populate list box .

    you mean there is no way to freeze first row in this case? so it should be to BAD  :(

    Sunday, November 24, 2013 11:56 AM
  • I'm sorry, but there is no way to freeze the first row if you're using AddItem to populate the list box. It only works if the RowSource is a cell range.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Nima Iranian Sunday, November 24, 2013 12:47 PM
    Sunday, November 24, 2013 12:13 PM
  • I suggest you put labels exactly above list box for the header items based on the display order of items in the listbox, no need for coding. You can change names of label through Caption. This answer might help others so i am adding it here. 




    Wednesday, June 22, 2016 8:56 AM
  • I never use RowSource, so if I need a header, I use labels lined up above the columns of my list box, exactly as Darshan suggests.

    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Thursday, June 30, 2016 1:13 PM
  • To work around not being able to freeze the first (header) row in a ListBox, create a single-row (row 1) list box immediately above the required ListBox.

    Monday, May 21, 2018 11:57 AM