Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Populating a listbox (XL97/WinNT)

    Could someone help me please? I'm not very good at XL VBA (I'm more used to Word!) but I need to have a listbox on a userform that picks up the names of the sheets in the workbook as its list. The names are all four figure years.

    Any help would be appreciated.
    Beryl M


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a listbox (XL97/WinNT)

    Hi Beryl,

    Pu this in the Form's initialize event:

    Private Sub UserForm_Initialize()
    Dim mysheet As Worksheet
    For Each mysheet In ThisWorkbook.Worksheets
    Me.ListBox1.AddItem mysheet.Name
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Populating a listbox (XL97/WinNT)

    The Code below placed in the Initialize Event of the UserForm will fill it with sheet Names.
    The Attached Small spreadsheet has an example User form in it, with associated Code.

    Private Sub UserForm_Initialize()
    'Combo Box on Form is Called cboSheets
    'This Code Fills the Combo with Worksheet Names Only

    Dim objSheet As Worksheet

    For Each objSheet In ActiveWorkbook.Worksheets
    cboSheets.AddItem objSheet.Name
    Next

    End Sub
    Attached Files Attached Files
    Andrew

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a listbox (XL97/WinNT)

    Ta muchly, I'll try that.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a listbox (XL97/WinNT)

    Also a good suggestion; I'm going to have to do some experimenting!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    By the way, can you exclude certain sheets from the list using this code? 'Cos after I posted my query, I noticed that there were two sheets lurking at the back that have different names and do not want to be in the listbox. I wondered whether it would be possible to maybe restrict the list to only entries of four characters, or maybe specify particular names that would be excluded?

    Many thanks
    Beryl M


  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Populating a listbox (XL97/WinNT)

    Beryl,

    the following should restrict the operation to sheets wirh a name of 4 characters. <pre>For Each objSheet In ActiveWorkbook.Worksheets
    If Len(objSheet.Name) = 4 Then
    ' add to listbox
    End If</pre>

    Andrew C

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a listbox (XL97/WinNT)

    Wonderful! That should do nicely!

    [thumbsup]
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •