Sheet Change Event Dubuggin: Restrict Cell Values toand Changing cells based on input

x needs to be a Variant rather than Long, then

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo dang
    Build
    Application.EnableEvents = False

    If Target.Column = 3 And Target.Row = TestsTaken + 7 Then
        item = Cells(Target.Row, 3)
        x = Application.Match(item, PTests, 0)
        If IsError(x) Then
            MsgBox "Please Insert Valid PTest"
            Cells(Target.Row, 3) = vbNullString
        Else

            Cells(Target.Row, 2) = Date + Time
            Cells(Target.Row, 1) = Target.Row - 6
            Cells(Target.Row, 3).Font.Color = 16711680
            Cells(Target.Row, 1).Resize(1, 2).HorizontalAlignment = xlCenter
            Cells(Target.Row, 6) = Application.Index(PTests, x, 0)
        End If
    End If

    TestsTaken = Sheet3.Cells(Distance, 1).End(xlDown)
    Build

dang:
    Application.EnableEvents = True


End Sub

I can't begin to emphasize enough that you really oughta stop using so many public variables. For instance why is x public?

also in this code

    With Sheets("Contents")
        For i = 1 To Sheets.Count
            Cells(i + 1, 2) = Sheets(i).Name
            Cells(i + 1, 1) = i
        Next i
    End With

your with statement doesn't do anything. the code oughta be

    With Sheets("Contents")
        For i = 1 To Sheets.Count
            .Cells(i + 1, 2) = Sheets(i).Name
            .Cells(i + 1, 1) = i
        Next i
    End With

note the periods that qualify the Cells property calls

Thank You and will update asap. I really appreciate it. Will edit the Public variables also. I'm rebuilding this workbook, and was trying to create efficiencies while referring to the old model, as I was continually using items like TestsTaken and MyTests, etc.

the change event occurs whenever you edit a cell (even if you don't actually change the value)

application.match and worksheetfunction.match do the same thing-the difference is in what happens when a match is not found. application.match returns an error value that you can test with IsError; worksheetfunction.match raises a run-time error that has to be handled with an On Error statement

sir?? ;-)

what's your reference for finding out the answer for the match difference?

experience. I think it's mentioned on Chip Pearson's site and a few others

can you clarify what you mean about the change event?

exactly what I said. a manual entry will trigger the change event unless you have events disabled

first remove the

application.enableevents = true

line from your Build sub or you may end up stuck in a loop you can't terminate

second your code only monitors one cell-column C and the row (teststaken + 7) which you update as soon as an entry is made in a row. so once you've made an entry you are only monitoring the cell below it. I don't really know why you set it up that way so can't advise what your best solution is-it may be just to monitor the whole column C

Quick question, why take off app.enabevent = true? Don't I have to cut it back on after I cut it off?

So I did that to ignore changes above the header. changed code to If

Target.Column = 3 And Target.Row > Distance Then

Worked perfectly for updating.

How would you do for delete on update

Possible Solution?

Private Sub Worksheet_Change(ByVal Target As Range)
    
 Application.EnableEvents = False
    Build
    
If Target.Column = 3 And Target.Row > Distance Then
        item = Cells(Target.Row, 3)
        If item = vbNullString Then
            Cells(Target.Row, 1).Resize(1, 67).Clear
            Exit Sub
        End If
        If IsError(Application.Match(item, PTests, 0)) Then
            MsgBox "Please Insert Valid PTest"
            Cells(Target.Row, 3) = vbNullString
        Else: x = WorksheetFunction.Match(item, PTests, 0)

        Cells(Target.Row, 2) = Date + Time
        Cells(Target.Row, 1) = Target.Row - 6
        Cells(Target.Row, 3).Font.Color = 16711680
        Cells(Target.Row, 1).Resize(1, 2).HorizontalAlignment = xlCenter
        Cells(Target.Row, 6) = PTestsAdmin(x)
        End If
    End If

    TestsTaken = Sheet3.Cells(Distance, 1).End(xlDown)
    Build

Application.EnableEvents = True

End Sub

that only re-enables events in the Else clause-you should always re-enable events!

the only reason I got an infinite loop in your last sample workbook was because you re-enabled events at the start of the Build routine but I assume you removed that like I said?

1 it's ma'am, not sir
2 you still haven't removed the

Application.EnableEvents = True

line from the start of the Build routine
3 are you actually experiencing problems? I'm not gonna just peer review your code for you ;-) (especially not with all those public variables-they give me a headache)

My apologies, ma'am .

So I did remove from top Application.EnableEvents = False, so change doesn't trigger anything else. I then turn it on at different points to ensure code will fire on next change. No peer review, definitely experiencing issues as I'm trying to learn and build. but all help is muuuuch appreciated and needed. I wish I could be anyone's peer on this forum. I'm building this as a study tool and to learn and then will try to build some things at work.

and I promise I'm working towards removing as many public variables and named ranges as possible, I'm just moving from one sheet to the next

latest file is too big for some reason, and found a random bug that deletes A and B of summary on sheet change, will update and attach. THANKS for all help so far

*Finally realized you meant, Application.EnableEvents = True at the top of the Build sub.........deleted and face palm