Excel VBA recalculate on all worksheets when open and tab change

Question

I have 13 worksheets with 12 of them for each month. There is a cell in each of the 12 sheets with the name of the month.

I've gotten a VBA script to test if the current month matches the cell and to change the tab color accordingly, but it requires a calculation in the sheet to update.

Private Sub Worksheet_Change(ByVal Target As Range)
If Format(Date, "MMMM") = Range("E3").Value Then
    Me.Tab.ColorIndex = 10
Else
    Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

If I stop updating last month's sheet and move onto the next sheet, both tabs would be highlighted because the previous sheet was not updated. I just want the current month worksheet to be highlighted.

Is there a way to recalculate all sheets on workbook open and tab change?

Answer 1

In ThisWorkbook module just put

Option Explicit

Private Sub Workbook_Open()
    Dim mySheet As Worksheet
    For Each mySheet In ThisWorkbook.Sheets
        If mySheet.Cells(3, 5).Value = Format(Date, "MMMM") Then
            mySheet.Tab.ColorIndex = 10
        Else: mySheet.Tab.ColorIndex = xlColorIndexNone
        End If
    Next
End Sub

Now it doesn't need to calculate anything and will just check all of the sheets each time it's opened.

Answer 2

Yes. In the VB Editor, go into the ThisWorkbook object. At the top of the code window, you'll see a dropdown box that says "(General)" or "Workbook". If it says "(General)", open the dropdown and choose "Workbook" instead. It may well insert a new sub for you immediately; if it does, just delete that.

Now, in the upper right, you have another dropdown that probably says "Open". Use the dropdown to choose "Open", then again to choose "Sheet Activate". This should insert two new subroutines into your code window - Workbook_Open and Sheet_Activate. Note that you do want to create these by using the dropdowns, not just by typing the names in - they may not work properly if you simply type them in.

You can now put any code you want into these subs, and the code will execute when it detects the event in question (ie opening the workbook or selecting any worksheet). The exact structure you requested would look something like this:

Private Sub Workbook_Open()
    Application.Calculate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.Calculate
End Sub

I'm not saying that's the most efficient way to do this, but that should answer your question.