Add name to combo box

[Av8tordude]

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

 

[AlphaFrog]

Try something like this...

Private Sub cboYear_Enter()
Dim Sh As Worksheet

If EE Then Exit Sub
EE = False
With cboYear
   [COLOR="Red"] .AddItem "New Report"[/COLOR]
    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]

Thank you AlphaFrog...works great!!!

 

[Norie]

I know the original question has been answered, but I was just curious what EE was for.
Is it to prevent duplication?

 

[Av8tordude]

Norie...Yes

 

[mikerickson]

Cross-posted
http://www.vbaexpress.com/forum/showthread.php?t=36889

 

[Av8tordude]

Sorry about the cross-post. I should have posted it. Thanks Mikerickson

 

[Norie]

Why not just clear the combobox or use some other event to populate?

To clear it:

Combobox1.Clear
 
' code to populate combobox

The event thing isn't so straightforward, but putting the code to populate the combobox in it's own Enter event proably isn't the best idea.

If it's on a userform why not try the userform's Initialize event.

 

[Av8tordude]

Hmmm...interesting. I'll try it...thanks Norie.

 

[djreiswig]

You could also use the index property to place the item at the top of the list.

cboYear.AddItem "New Report", 0

</pre>