now() minus 1 month color sheets vba

[KlausW]

Hi, I use this VBA code to open and color the sheet for the current month.
It works as it should.
But the sheets from the previous month still has the same color as the current month.
Can someone help with the fact that the previous month has no color.
Any help will be appreciated.
Best Regards Klaus W

Option Explicit

Private Sub Workbook_Open()

Sheets(Format(Now, "mmmm")).Activate

Sheets(Format(Now, "mmmm")).Select

ActiveSheet.Tab.ColorIndex = 4

End Sub

 

[Fluff]

How about

Private Sub Workbook_Open()
   Sheets(MonthName(Month(Date), 1)).Tab.ColorIndex = 4
   Sheets(MonthName(Month(Date) - 1, 1)).Tab.ColorIndex = xlNone
End Sub

 

[KlausW]

How about

Private Sub Workbook_Open()
   Sheets(MonthName(Month(Date), 1)).Tab.ColorIndex = 4
   Sheets(MonthName(Month(Date) - 1, 1)).Tab.ColorIndex = xlNone
End Sub

Hi Fluff I'm getting this error message (See image) and can't figure out why. The sheets are in order of months starting with April, May, June ...... and ending with March. Best regards Klaus W

 

[Alex Blakenburg]

Are you sheet names in English ? If so what language do you have your computer set to, is is also English ?

 

[KlausW]

Are you sheet names in English ? If so what language do you have your computer set to, is is also English ?

No the sheets name is in Danish, so that's maybe why?

 

[KlausW]

Are you sheet names in English ? If so what language do you have your computer set to, is is also English ?

The anguage on my PC is also in Danish

 

[Alex Blakenburg]

In principle if they are both Danish it should work but since there is one more place that can impact it, what do you see if you go into Format > Date ?
Is the 2nd format that you see which shows the Day in words and the Full month name in Danish ?

 

[Alex Blakenburg]

I think the current issue is that the are using the full month name and the ",1" in Fluff's code tells it to use the abbreviated name. It needs to be ",0" (or not there at all since 0/False is the default)
Since the code will also have an issue with January since Monthname(1-1) will error out and to save some time in case you have other issues, I fleshed out the code a bit.

Private Sub Workbook_Open()
    Dim dt As Date
    Dim MthNo As Long, MthNoPrev As Long
   
    dt = Date
    MthNo = Month(dt)
   
    On Error GoTo ErrHandler
    Sheets(MonthName(MthNo, 0)).Tab.ColorIndex = 4

    If MthNo = 1 Then
        MthNoPrev = 12
    Else
        MthNoPrev = MthNo - 1
    End If
    Sheets(MonthName(MthNoPrev, 0)).Tab.ColorIndex = xlNone
   
    Exit Sub
   
ErrHandler:
    If MthNoPrev = 0 Then
        MsgBox "The sheet " & MonthName(MthNo, 0) & " not found"
    Else
        MsgBox "The previous month sheet " & MonthName(MthNoPrev, 0) & " not found"
    End If
End Sub

 

[KlausW]

I think the current issue is that the are using the full month name and the ",1" in Fluff's code tells it to use the abbreviated name. It needs to be ",0" (or not there at all since 0/False is the default)
Since the code will also have an issue with January since Monthname(1-1) will error out and to save some time in case you have other issues, I fleshed out the code a bit.

Private Sub Workbook_Open()
    Dim dt As Date
    Dim MthNo As Long, MthNoPrev As Long
  
    dt = Date
    MthNo = Month(dt)
  
    On Error GoTo ErrHandler
    Sheets(MonthName(MthNo, 0)).Tab.ColorIndex = 4

    If MthNo = 1 Then
        MthNoPrev = 12
    Else
        MthNoPrev = MthNo - 1
    End If
    Sheets(MonthName(MthNoPrev, 0)).Tab.ColorIndex = xlNone
  
    Exit Sub
  
ErrHandler:
    If MthNoPrev = 0 Then
        MsgBox "The sheet " & MonthName(MthNo, 0) & " not found"
    Else
        MsgBox "The previous month sheet " & MonthName(MthNoPrev, 0) & " not found"
    End If
End Sub

Dear Alex Blankenburg, it works just as it should. Thank you. Good day from Denmark Regards Klaus W

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.