worksheet_change() isn't changing!

[ieJasonW]

Ok, I have a spreadsheet that allows someone to update cell AF,20 with a number. When they do that, the worksheet_change() function is supposed to format a range of cells based on the input value.

Well, I tested the code by changing the number -- and I got an error. I fixed the problem, but now the worksheet_change() won't change my spreadsheet! I hit STOP. But it won't work.

ieJasonW

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Integer, j As Integer, nl As Integer
  Dim ns As Integer
  
If Not ((Target.Address = "$AF$20") Or (Target.Address = "$AG$35")) Then
    Exit Sub
End If

If Target.Address = "$AF$20" Then
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'Must turn this off to alter the worksheet
    
    ns = Cells(20, 32).Value 'Number of Stages
    
        'clears the cells gives them plain formatting
        For i = 1 To 12 'upto 12 stages
            For j = 1 To 7 'for each of 6 stage properties and stage number column
                Cells(17 + j, 34 + i).ClearContents
                Cells(17 + j, 34 + i).Borders.ColorIndex = 2
                Cells(17 + j, 34 + i).Interior.ColorIndex = 2
                Cells(17 + j, 34 + i).Font.ColorIndex = xlColorIndexAuto
            Next j 'Next row
        Next i 'Next column
        
        'For ns number of stages, enter in the stage number title in the appropriate column
        For i = 1 To ns
                Cells(18, 34 + i).Value = i
        Next i 'Next layer
        
        'Formats the inner cells of the Injection Stage Properties table
        For i = 1 To ns 'for ns number of stages
            For j = 1 To 6 'for 6 stage properties
                Cells(18 + j, 34 + i).Interior.Color = RGB(255, 255, 204) 'Set interior color to peach
                Cells(18 + j, 34 + i).Select 'select new cell
         
                With Selection
                    .ColumnWidth = 9
                    With .Borders 'give selection a border
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .Color = RGB(192, 192, 192) 'set color to light grey
                    End With
                End With
            Next j 'Next row
        Next i 'Next column
        
    Cells(20, 32).Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    End If 'End of Dynamic Formatting
End Sub

 

[shg]

In the VBE, do Ctrl+G to see the Immediate window if it's not already visible.

In there, paste

Application.EnableEvents = True

and hit Enter

 

[Datsmart]

Sounds like Events did not get turned back on after your error.

Type and Enter the following line in the Immediate Window of the VBA editor.
Application.EnableEvents = True
(Ctrl-G will open the Immediate window…)

 

[ieJasonW]

Sweet! Thanks! Haha. so simple!

ieJasonW