Add Numerical Tab Names in Combo box

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
805
My workbook sheet tabs would be named by Year (i.e. 2018, 2019, etc). Currently I have one sheet (i.e. 2019). I would like to display in a combo box the previous 2yrs (i.e. 2018 & 2017) and also the following year (i.e. 2020). Maximum years displayed should be limited to 4. (i.e. 2017 - 2020) because the workbook will only have 4 worksheets.


This code adds the years to the combo box.

Code:
Dim ws As WorksheetDim iloop As Integer
 
With cboYear
    For iloop = Year(Date) + 1 To Year(Date) - 2 Step -1
        .AddItem iloop
    Next
End With
I have the code below that displays all sheets that are currently in the workbook (which works as expected).

Code:
For Each ws In ActiveWorkbook.Sheets    If ws.Visible <> xlSheetVeryHidden Then cboYear.AddItem ws.Name
Next

If I would like to merge the two codes that will display the years as previously explained. if I have 4 worksheets then, then display the following year. (note: when the following sheet is added, the earliest sheet will be deleted). Is this possible.

Thank you kindly
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
Do you currently have sheets called 2017,2018,2019 & 2020?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
In that case how can you combine the two codes?
What is that you are trying to do?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,372
Messages
5,468,233
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top