Worksheet_Change - Error with nested If-then inside Case

Hello. I have an issue where Excel 2007 is crashing while running the following code. Here is what I ultimately want to achieve:

In cell C18, I have a drop-down list with 4 values (5 if you include the default blank). Once the user selects from that list, I want VB code to analyze each cell in range H4:H33. If any of those cells are blank, then make the cell next to it blank else make it "no".

Simply put, any time a user changes the value in C18, it should run the code once to make a select group of cells either "no" or blank.

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim rngCellWithEventName As Range

Set rngCellWithEventName = ActiveSheet.Range("C18")

If ActiveCell = ActiveSheet.Range("C18") Then
    Select Case rngCellWithEventName
 	   Case "Athlete"
        	AthleteStuffToDo
    	Case "Culture":
    	Case "Event":
    	Case "SBM":
    	Case Else:      Exit Sub
    End Select
End If

Application.ScreenUpdating = True
End Sub

Function AthleteStuffToDo()
Dim rngCellBeingChecked As Range
    For Each rngCellBeingChecked In Range("H4:H33")
        If rngCellBeingChecked.Value = "" Then
            rngCellBeingChecked.Offset(0, 1).Value = ""
        Else
            rngCellBeingChecked.Offset(0, 1).Value = "no"
        End If
    Next rngCellBeingChecked
End Function

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


You need to disable events as your function is altering cells which will cause the event to fire again.

Also you jump out of the event on the Else case without turning screen updating back on.

So I did some more revisions and I most definitely have determined that the error is directly related to "writing" new information in the cells (ie: setting their value to either "no" or "")

Here is an update to the code. Take note that in the function, "UpdateYesNoCells()", I have two changes to the cells that do not impact their Value... this code works just fine. The issue, once again, is that I want to write either "no" or leave it blank (bold below):

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Dim rngCellWithEventName As Range

Set rngCellWithEventName = ActiveSheet.Range("C18")

If ActiveCell = ActiveSheet.Range("C18") Then 'rngCellWithEventName Then
UpdateYesNoCells
End If

Application.ScreenUpdating = True
End Sub

Function UpdateYesNoCells()
Dim rngCellBeingChecked As Range

    For Each rngCellBeingChecked In Range("H4:H33,M4:M33,H36:H65,M36:M45,M48:M53")
        If rngCellBeingChecked.Value = "" Then
            rngCellBeingChecked.Offset(0, 1).Interior.ColorIndex = 2
            rngCellBeingChecked.Offset(0, 1).Locked = True
            rngCellBeingChecked.Offset(0, 1).Value = ""
        Else
            rngCellBeingChecked.Offset(0, 1).Interior.ColorIndex = 19
            rngCellBeingChecked.Offset(0, 1).Locked = False
            rngCellBeingChecked.Offset(0, 1).Value = "no"
        End If
    Next rngCellBeingChecked
End Function

You did not add the code I suggested to stop the event from firing when changing the Value property in the routine.

Sub Worksheet_Change(ByVal Target As Range)
'Application.ScreenUpdating = False

Dim rngCellWithEventName As Range

Set rngCellWithEventName = ActiveSheet.Range("C18")

If ActiveCell = ActiveSheet.Range("C18") Then 'rngCellWithEventName Then
Application.EnableEvents = False
UpdateYesNoCells
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
End Sub

Also you know that in the change event the active cell will not be the cell that has just changed. Because in order to change you need to exit the cell and when you exit the cell it is no longer the active cell.