Excel VBA For Each Loop Skipping Checkboxes

Question

I am having an issue with a to-do list I am making in Excel. Currently, I am trying to create VBA code that updates the Cell Interior Fill Color whenever a value in a cell is changed. In other words, Column F contains the Due Date of the item. If the item is due that day, the color of the entire row should be red. If the item is due 30+ days from then, it should be green, and different stops in between. The to-do list should be dynamic. I.e. A user can add new rows at any time. Because VBA would not be able to know where the last row of data is, I have set the code to rely on the location of checkboxes in the left most column of the list. I'd post an image, but I just started on SO, so I don't have that ability yet. Essentially, there's a header row in Row 2, There are 6 Columns starting in Column B, going to Column G, "Check, Status, Task, Notes, Due, Days Left".

There's a list of 12 Entries. Entries 1-11 work fine and represent the 11 different possible colors a row can be. Row 12 was drag and dropped from row 11 with all functions and the checkbox included. Row 12 doesn't update when it the VBA is called.

My code uses a For Each loop to go through every checkbox in the list, and update the color based on the due date. For most of the checkboxes, it works fine, and msgbox's located throughout indicate that it is making it through the loop fine. However, when I add a new row, by selecting the entire row and dragging it down in the worksheet, the VBA code stops recognizing the new checkboxes only. It can iterate through all the boxes except the new ones. I have attached my VBA code as well. If anyone can tell me how to fix the unrecognized (unloved) checkbox, I'd greatly appreciate it!

Code to Check for Update This code is in the Worksheet1 section of VBA, not in a different module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Volatile True
    Application.ScreenUpdating = False
    Call Checkbox_Due_Color
    Application.ScreenUpdating = True
End Sub

Code to Set Range and act as Decision Handler I believe problem to be in the loop in this code. MsgBox myCheckboxLoc.Address Does not fire on last checkbox. This is located in Module 2.

Option Explicit

Sub Checkbox_Due_Color()
'===============================================================
'Determines how to update the color to indicate days left.
'===============================================================
    Application.Volatile True
    Dim myCheckbox As CheckBox
    Dim myCheckboxLoc As Range
    Dim myDaysLeft As Integer
    Dim myCheckboxName As String

    For Each myCheckbox In ActiveSheet.CheckBoxes
            Set myCheckboxLoc = Range(myCheckbox.TopLeftCell.Address & ":" & myCheckbox.TopLeftCell.Offset(0, 5).Address)
            MsgBox myCheckboxLoc.Address
            If IsEmpty(myCheckbox.TopLeftCell.Offset(0, 4)) = True Then
                'MsgBox "IF TRUE"
                myCheckboxLoc.Interior.ColorIndex = xlNone
            Else
                'MsgBox "IF False"
                myDaysLeft = myCheckbox.TopLeftCell.Offset(0, 4).Value - Date
                'MsgBox myDaysLeft
                Call Update_Color(myCheckboxLoc, myDaysLeft)
            End If
    Next myCheckbox
End Sub

Code to Determine what Color to Use This code works fine, included for clarity's sake. Also located in Module 2.

Sub Update_Color(myCheckboxLoc As Range, myDaysLeft As Integer)
'===============================================================
'Change background color of cell related to days left until due.
'===============================================================

    Select Case myDaysLeft
        Case Is <= 0
            myCheckboxLoc.Interior.Color = RGB(255, 0, 0)
        Case 1
            myCheckboxLoc.Interior.Color = RGB(255, 50, 0)
        Case 2
            myCheckboxLoc.Interior.Color = RGB(255, 100, 0)
        Case 3 To 4
            myCheckboxLoc.Interior.Color = RGB(255, 150, 0)
        Case 5 To 6
            myCheckboxLoc.Interior.Color = RGB(255, 200, 0)
        Case 7 To 9
            myCheckboxLoc.Interior.Color = RGB(255, 210, 0)
        Case 10 To 13
            myCheckboxLoc.Interior.Color = RGB(255, 230, 0)
        Case 14 To 20
            myCheckboxLoc.Interior.Color = RGB(255, 255, 0)
        Case 21 To 29
            myCheckboxLoc.Interior.Color = RGB(175, 255, 0)
        Case Else
            myCheckboxLoc.Interior.Color = RGB(0, 255, 0)
        End
    End Select

End Sub

Nathan Dudley · Accepted Answer · 2014-07-22 21:37:29Z

1

Your code is breaking in the Update_Color sub procedure. You don't need to put "End" before "End Select". Your case statement should look like this:

 Select Case myDaysLeft
    Case Is <= 0
        myCheckboxLoc.Interior.Color = RGB(255, 0, 0)
    Case 1
        myCheckboxLoc.Interior.Color = RGB(255, 50, 0)
    Case 2
        myCheckboxLoc.Interior.Color = RGB(255, 100, 0)
    Case 3 To 4
        myCheckboxLoc.Interior.Color = RGB(255, 150, 0)
    Case 5 To 6
        myCheckboxLoc.Interior.Color = RGB(255, 200, 0)
    Case 7 To 9
        myCheckboxLoc.Interior.Color = RGB(255, 210, 0)
    Case 10 To 13
        myCheckboxLoc.Interior.Color = RGB(255, 230, 0)
    Case 14 To 20
        myCheckboxLoc.Interior.Color = RGB(255, 255, 0)
    Case 21 To 29
        myCheckboxLoc.Interior.Color = RGB(175, 255, 0)
    Case Else
        myCheckboxLoc.Interior.Color = RGB(0, 255, 0)
End Select

I recreated your worksheet and everything worked fine after I removed that line. I also recommend you use "Debug.print" instead of "MsgBox" for debugging purposes.

You also said "Because VBA would not be able to know where the last row of data is, I have set the code to rely on the location of checkboxes in the left most column of the list." That's not quite accurate. You can use the End property to figure out the last row of data. Like this:

Sub getTaskRange()

  Dim cell As Range
  Dim taskRange As Range
  'Set the range object dynamically
  Set taskRange = ActiveSheet.Range(Cells(2, "d"), Cells(Rows.Count, "d").End(xlUp))

  'Print the contents of each cell to the immediate window
  For Each cell In taskRange
    Debug.Print cell.Value
  Next cell

End Sub

answered Jul 22, 2014 at 21:37

Nathan Dudley

5928 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KevinM Over a year ago

This is wonderful! Thank you Nathan. Perchance, could you tell me why it is breaking at End? I've seen End used on several VBA Case examples, and it seems to work on every checkbox up until that point.

Nathan Dudley Over a year ago

In my understanding, "End" will completely halt execution. In the Case Statements you've seen, it was probably the programmer's decision to end the program immediately if the Case Else condition was met. In your case, I'm not sure why the newly added check box met the "Case Else" condition unless the new task had a due date of over 29 days.

Nathan Dudley Over a year ago

The keyword "End" has nothing to do with the case statement, but is an independently executable command - it should be on the same indent level as the line above it.

KevinM Over a year ago

Ahh, that makes a lot of sense. as I said earlier, I've only started playing around with VBA recently and am still trying to get the hang of things. Thank you so much for your clarity and patience! I appreciate it!

Collectives™ on Stack Overflow

Excel VBA For Each Loop Skipping Checkboxes

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related