Add Numerical Tab Names in Combo box

[Av8tordude]

I have sheet tabs name by the year. Only 1 sheet tab will be visible. All other tabs will be hidden. The code below will populate the userform with years (i.e. 2019, 2018, 2017, etc). My current sheet tab (2018) is visible. I would like the combo box to not display the year in the combo box of the visible sheet (2018). (i.e. If sheet tab 2018 is visible, the dropdown should only display 2019, 2017, 2016). Is this possible?

Thanks for you help

Dim iloop as interger

With cboYear    For iloop = Year(Date) - 2 To Year(Date) + 1 Step 1
        .AddItem iloop
    Next
End With

 

[My Aswer Is This]

Sounds like you only want the Hidden sheet names added to the ComboBox.
Try this:

Put this script in your UserForm it will run when the UserForm is opened.

Private Sub UserForm_Initialize()
'Modified  7/3/2019  9:15:34 PM  EDT
Dim i As Long
For i = 1 To Sheets.Count
    If Sheets(i).Visible = False Then cboYear.AddItem Sheets(i).Name
Next
End Sub

 

[Av8tordude]

Well...not that simple. The loop creates 4 years starting from the current year (i.e 2017, 2018, 2019, 2020). The workbook will start with 1 sheet tab (i.e. 2018). When I create a new tab (i.e. 2019), 2018 tab is hidden and only 2019 is visible. Even though there are 2 sheet tabs in the workbook, I want the combo box to display the years aforementioned above, except the visible sheet.

So, basically I currently have 2 sheets (2017 &2018). 2018 sheet tab is only sheet visible. In the combo box I want to see all the years aforementioned above except the current visible sheet. So the combo box, I should only see 2017, 2019, 2020

 

[My Aswer Is This]

I'm not sure what the dates have to do with it. You said:

In the combo box I want to see all the years aforementioned above except the current visible sheet

So you only want to see the Hidden sheets in the ComboBox

If not then what sheets do you want to see in the ComboBox?

Or when you say Visible sheet do you mean Active sheet.

​

 

[Av8tordude]

I'll see if I can explain it differently...

When the workbook first opens, it will only have 1-sheet. That sheet will be name 2019.
I want to add another worksheet and it will be renamed 2018. (I have this code already) I click drop down menu and the code below generates the years available (2017,2018,2019,2020)

[COLOR=#333333]Dim iloop as interger[/COLOR]
With cboYear    For iloop = Year(Date) - 2 To Year(Date) + 1 Step 1
    .AddItem iloop
Next 
End With
[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR]Since I already have a sheet name 2018, I don't want to see 2018 in the drop down selection. I only want see 2017, 2019, 2020. This is to prevent creating sheets with duplicate names


The code you have adds worksheets to the dropdown menu, however, if there is only 1 sheet available, it will only add that year (2018). I need to see the other years in the drop down menu to be able to create a new sheet with the selected year.

 

[Fluff]

If you start with sheet 2019 & add sheet 2018, why should 2019 still in the combobox?

 

[Av8tordude]

I'll see if I can explain it differently...

When the workbook first opens, it will only have 1-sheet. That sheet will be name 2019.
I want to add another worksheet and it will be renamed 2018. (I have this code already) I click drop down menu and the code below generates the years available (2017,2018,2019,2020)

[COLOR=#333333]Dim iloop as interger[/COLOR]
With cboYear    For iloop = Year(Date) - 2 To Year(Date) + 1 Step 1
    .AddItem iloop
    Next 
End With

Since I'm actively viewing the 2018 sheet, I don't want to see 2018 in the drop down selection. I only want see 2017, 2019, 2020.

If I add another sheet (2019), I use a code to create a new sheet and rename it 2019 (I have code already). Now I have two sheets.

When I click the drop down menu, I do not want to see 2019, since I'm actively viewing that sheet. I should only see in the drop down menu 2017 (has not been created), 2019, 20120 (has not been created).

If I add another sheet (2017), I use a code to create a new sheet and rename it 2017 (I have code already). Now I have 3 sheets.

When I click the drop down menu, I do not want to see 2017, since I'm actively viewing that sheet. I should only see in the drop down menu 2018, 2019, 20120 (has not been created).

 

[Av8tordude]

If you start with sheet 2019 & add sheet 2018, why should 2019 still in the combobox?

This was my point. I don't want to see 2019 if I'm actively viewing it. I still need to see the other years to either create the sheet if not already created or select the year if already created.

 

[Fluff]

In that case try

   For i = Year(Date) - 2 To Year(Date) + 1
      If ActiveSheet.Name <> CStr(i) Then
         Me.ComboBox1.AddItem i
      End If
   Next i

 

[Av8tordude]

In that case try

   For i = Year(Date) - 2 To Year(Date) + 1
      If ActiveSheet.Name <> CStr(i) Then
         Me.ComboBox1.AddItem i
      End If
   Next i

That is it!!!! Thank you Fluff!! Perfect!!!