+ Reply to Thread
Results 1 to 3 of 3

Worksheet_Change Event won't run more than once

Hybrid View

  1. #1
    Registered User
    Join Date
    07-08-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Worksheet_Change Event won't run more than once

    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
    Last edited by David McMillon; 07-08-2010 at 04:02 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Worksheet_Change Event won't run more than once

    You disable events and never reenable them.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-08-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Worksheet_Change Event won't run more than once

    Oh wow, I feel dumb now. Thank you for noticing that. I re-enabled the events and now the problem is solved. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1