CODEWITHSUNDEEP
vbaexcel
RabHillcrest
RabHillcrest

Reputation: 11

Refresh all VBA Codes when opening Spreadsheet

I am new to VBA in excel but have written a couple of different codes in my spreadsheet which work exactly as required when testing. Both of my codes change the colour of tabs dependent on dates and the number of days left until something happens. Here is one example:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim daysLeft As Integer
    daysLeft = 100 'just a number larger than you need
    For i = 6 To 29
        Select Case Range("C" & i).Value
        Case "Due in 5 Days"
            If daysLeft >= 5 Then daysLeft = 5
        Case "Due in 4 Days"
            If daysLeft >= 4 Then daysLeft = 4
        Case "Due in 3 Days"
            If daysLeft >= 3 Then daysLeft = 3
        Case "Due in 2 Days"
            If daysLeft >= 2 Then daysLeft = 2
        Case "Due Tomorrow"
            If daysLeft >= 1 Then daysLeft = 1
        Case "Due Today"
            If daysLeft >= 0 Then daysLeft = 0
        End Select
    Next
    Select Case daysLeft
        Case 100
            Me.Tab.ColorIndex = xlColorIndexNone
        Case 1 To 5
            Me.Tab.ColorIndex = 45
        Case 0
            Me.Tab.ColorIndex = 3
    End Select
End Sub

The only problem is that the above code does not refresh each day when I open the spreadsheet. The tab colour still stays as it was when I inserted and tested the code and does not change each day as 'todays' date changes. The tab colour should change from standard, to orange, to red as the days go down.

Can anyone provide some help? I also have a similar code on another tab of the spreadsheet with the same problem. Any help greatly appreciated for a novice!

Thanks in advance :-)

Upvotes: 1

Views: 115

Answers (1)

DisplayName
DisplayName

Reputation: 13386

just turn your event handler routine to a normal sub accepting a worksheet object as parameter:

Private Sub ColorSheets(sht As Worksheet)

    Dim i As Integer
    Dim daysLeft As Integer
    daysLeft = 100 'just a number larger than you need

    With sht
        For i = 6 To 29
            Select Case .Range("C" & i).Value
            Case "Due in 5 Days"
                If daysLeft >= 5 Then daysLeft = 5
            Case "Due in 4 Days"
                If daysLeft >= 4 Then daysLeft = 4
            Case "Due in 3 Days"
                If daysLeft >= 3 Then daysLeft = 3
            Case "Due in 2 Days"
                If daysLeft >= 2 Then daysLeft = 2
            Case "Due Tomorrow"
                If daysLeft >= 1 Then daysLeft = 1
            Case "Due Today"
                If daysLeft >= 0 Then daysLeft = 0
            End Select
        Next
        Select Case daysLeft
            Case 100
                .Tab.ColorIndex = xlColorIndexNone
            Case 1 To 5
                .Tab.ColorIndex = 45
            Case 0
                .Tab.ColorIndex = 3
        End Select
    End With

End Sub

and then add this event handler routine to your ThisWorkbook code pane:

Private Sub Workbook_Open()
    Dim sh As Worksheet
    For Each sh In Worksheets ' loop through worksheets
        ColorSheets sh ' call your routine passing current sheet
    Next
End Sub

Upvotes: 2

Related Questions