Consulting

Results 1 to 6 of 6

Thread: Solved: Add name to combo box

  1. #1

    Solved: Add name to combo box

    I have this code that displays the sheet tab names in a combo box. How do I add the name "New Report" in the combo box and display it at the top of the list in the combo box? (Note: there is not sheet named New Report)

    i.e.

    New Report
    Yr '2010
    Yr '2011
    [VBA]Private Sub cboYear_Enter()
    Dim Sh As Worksheet

    If EE Then Exit Sub
    EE = False
    With cboYear
    For Each Sh In ActiveWorkbook.Sheets
    If ((Sh.Name <> "Security") And (Sh.Name <> "f2106")) Then
    .AddItem "Yr '" & Sh.Name
    End If
    Next
    End With
    EE = True
    End Sub[/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Private Sub cboYear_Enter()
    Dim Sh As Worksheet

    If EE Then Exit Sub
    EE = False
    With cboYear

    .Clear
    .AddItem "New Report"
    For Each Sh In ActiveWorkbook.Sheets

    If ((Sh.Name <> "Security") And (Sh.Name <> "f2106")) Then

    .AddItem "Yr '" & Sh.Name
    End If
    Next
    End With
    EE = True
    End Sub [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thank you XLD.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I have just looked again at your code. Aren't you setting EE the wrong way around?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    I don't think I so, but I've been wrong before. Before i inserted that part of the code, each time i entered the combo box, the names would duplicate themselves and get displayed multiple times.

    i.e.

    New Report
    Yr '2010
    New Report
    Yr ' 2010

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    av8todude,
    You cross-posted this at Mr. Excel
    http://www.mrexcel.com/forum/showthread.php?t=540839

    When one cross-posts, one is supposted to post a link to the other thread(s).
    Please read the file in the link, it explains that we're not mad, just frustrated (in general) by improper cross-posts.
    How To Cross Post Politely

    Oh, and to add a bit to the OP question, using the pvargIndex argument of .AddItem method will let you add a list item at the top of a list/combo box list, regardless of what is already in the list.
    [vba]ListBox1.AddItem "New Report", 0[/vba]

Posting Permissions

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