Sub changed to Sub Worksheet_Change crashes Excel...

[PaulF]

Hello all,

VBA rookie here:

This sub work as needed:

Sub GetUniquePP1CatList()
Dim D As Object, C As Variant, i As Long, lr As String
Set D = CreateObject("Scripting.Dictionary")
Sheets("Pay Period 1").Select

' Vendor Release Summary UNIQUE LIST
lr = "L" & Range("C6").End(xlDown).Row + 1
C = Range("C6:" & lr)
For i = 1 To UBound(C, 1)
  D(C(i, 1)) = 1
Next i
Sheets("Pay Period 1").Range("I6:I100").ClearContents
Sheets("Pay Period 1").Range("I6").Resize(D.Count) = Application.Transpose(D.Keys)

End Sub

I want it to run when the this sheet is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim D As Object, C As Variant, i As Long, lr As String
Set D = CreateObject("Scripting.Dictionary")
Sheets("Pay Period 1").Select

' Vendor Release Summary UNIQUE LIST
lr = "L" & Range("C6").End(xlDown).Row + 1
C = Range("C6:" & lr)
For i = 1 To UBound(C, 1)
  D(C(i, 1)) = 1
Next i
Worksheets("Pay Period 1").Range("I6:I100").ClearContents
Worksheets("Pay Period 1").Range("I6").Resize(D.Count) = Application.Transpose(D.Keys)

End Sub

^^ Crashes Excel...

 

[NARAYANK991]

Hi Paul ,

A Worksheet_Change procedure is an event procedure , which means it will run each time an event is triggered ; in this case , the event is a change to any cell in the worksheet where this procedure is placed.

Thus , any Worksheet_Change procedure that changes any worksheet cell content is going to trigger the same Worksheet_Change event , which will then cause the Worksheet_Change macro to run , thus creating a kind of an infinite loop. This will either lock up Excel or crash Excel when it runs out of resources.

Hence , if your Worksheet_Change procedure needs to change any worksheet cell content , just surround the line of code which is doing this by the following two lines of code :

Application.EnableEvents = False

Line(s) of code which changes worksheet cell(s)

Application.EnableEvents = True

Narayan

 

[PaulF]

Hi Paul ,

A Worksheet_Change procedure is an event procedure , which means it will run each time an event is triggered ; in this case , the event is a change to any cell in the worksheet where this procedure is placed.

Thus , any Worksheet_Change procedure that changes any worksheet cell content is going to trigger the same Worksheet_Change event , which will then cause the Worksheet_Change macro to run , thus creating a kind of an infinite loop. This will either lock up Excel or crash Excel when it runs out of resources.

Hence , if your Worksheet_Change procedure needs to change any worksheet cell content , just surround the line of code which is doing this by the following two lines of code :

Application.EnableEvents = False

Line(s) of code which changes worksheet cell(s)

Application.EnableEvents = True

Narayan

New lessons learned... Thank you for the explanation as that makes is clear... crystal clear sir