Hi,
I'm trying to set up some code to run whenever a cell is changed. When I open the workbook and change the cell, the code works, but if I change the cell again, the code doesn't run. If I close the workbook and reopen it, the code again runs perfectly once and then doesn't run if the cell is changed a second time. There are no errors, it seems that the event simply doesn't initiate.
Here is my code for reference:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$17" Then
Dim Loc As String
Loc = Target.Text
Dim Count As Integer
Count = 101
Do
If ThisWorkbook.Sheets("IO").Cells(Count, 1) <> "" Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim wbOpen As Workbook, File_Name As Variant, strWbk As String
strWbk = ThisWorkbook.Worksheets("IO").Cells(Count, 10).Value
Set wbOpen = Workbooks.Open(strWbk)
Dim Eligible As Boolean: Eligible = False
For Each cl In wbOpen.Sheets("Tariff").Range("Location")
If cl.Value = Loc Then
Eligible = True
Exit For
End If
Next cl
wbOpen.Close
ThisWorkbook.Worksheets("IO").Cells(Count, 20) = Eligible
Count = Count + 1
Else
End If
Loop Until ThisWorkbook.Sheets("IO").Cells(Count, 1) = ""
'Sort the Fields by Eligibility
Range("A101:AI2000").Select
ActiveWorkbook.Worksheets("IO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("IO").Sort.SortFields.Add Key:=Range("T101:T2000") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("IO").Sort
.SetRange Range("A101:AI2000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D17:E17").Select
Else
Exit Sub
End If
End Sub
Bookmarks