PDA

View Full Version : Solved: Add name to combo box



av8tordude
04-03-2011, 12:51 PM
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
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

Bob Phillips
04-03-2011, 12:53 PM
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

av8tordude
04-03-2011, 12:57 PM
Thank you XLD. :friends:

Bob Phillips
04-03-2011, 01:48 PM
I have just looked again at your code. Aren't you setting EE the wrong way around?

av8tordude
04-03-2011, 01:58 PM
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

mikerickson
04-03-2011, 02:37 PM
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 (http://www.mrexcel.com/forum/showthread.php?t=540839)

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.
ListBox1.AddItem "New Report", 0