Add name to combo box

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
949
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


Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Try something like this...
Code:
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
I know the original question has been answered, but I was just curious what EE was for.
Is it to prevent duplication?
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
949

ADVERTISEMENT

Norie...Yes
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
949

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Why not just clear the combobox or use some other event to populate?

To clear it:
Code:
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.
 

djreiswig

Well-known Member
Joined
Mar 13, 2010
Messages
523
You could also use the index property to place the item at the top of the list.

Code:
cboYear.AddItem "New Report", 0
</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top