lundi 24 mai 2021

If-Then statement is looping between Sub and Calling Procedure

Folks, I need some help...

I have a sub-procedure that I'm using to detect data-validation in Column G of my sheet and, dependent on a value (STD or OPT) in Column C, fires a msgbox. This part is working, but I'm trying to get a value (PAINT, WHT) to return (if this changes) to Column G, when switching Column C from "OPT" back to "STD." The logic doesn't seem that complicated, but when I place an IF...Then statement, it constantly wants to return to the calling Procedure and repeats the process for each subsequent line in my Specification Sheet. Essentially, when I run the main procedure, it calls and runs the Sub, but then I have to hit Enter about 45 times, before the operation (within the IF Statement) completes and the entire main Procedure finishes. Can someone view my code to see why this is occurring? I've exhausted my knowledge and spent WAY too much time trying to rectify the issue.

This is the code in my Sub:

Sub VerifyDVG()

    Dim aCl As Range
    Dim ws As Worksheet
    Dim dvG As Range
    Dim Cv As String
    Dim msgS As String
    Dim msgO As String
    Dim Title As String
    Dim Ntfy As String
    
    Set aCl = ActiveCell
    Set ws = Sheets("53SCT-FRCOHAR")
    
    msgS = "Please verify the correct Specification, by selecting it from the Finish Options List."
    msgO = "Please choose a Specification from the Finish Options List."
    Title = "Verify Specifications"
    
    ws.Unprotect ("******")
    On Error GoTo noval
    
    Set dvG = Range("G:G").Cells.SpecialCells(xlCellTypeAllValidation)
    Cv = aCl.Value
    
        If Intersect(dvG, aCl.Offset(0, 4)) Is Nothing Then GoTo noval
            If Cv = "STD" And aCl.Row = 53 Then
                Ntfy = MsgBox(msgS, vbExclamation + vbOKOnly, Title)
                aCl.Offset(0, 4).Value = "PAINT, WHT"
            ElseIf Cv = "STD" And aCl.Row <> 53 Then
                Ntfy = MsgBox(msgS, vbExclamation + vbOKOnly, Title)
                aCl.Offset(0, 4).Value = "UC"
            Else
                Ntfy = MsgBox(msgO, vbExclamation + vbOKOnly, Title)
            End If
            
        ws.Protect ("******")
        Exit Sub
noval:
        ws.Protect ("******")
        MsgBox (Null)
    
    On Error GoTo 0
    
End Sub

When it reaches this line:

aCl.Offset(0, 4).Value = "PAINT, WHT"

or

aCl.Offset(0, 4).Value = "UC"

...it changes the value for Column G successfully, but immediately returns to the calling procedure and continues looping until it reaches the end of my document.

Note, the calling procedure does have a parameter set:

Private Sub Worksheet_Change(ByVal Target As Range)

Would that have anything to do with it? Or how I have my other calls stacked within that? Or, perhaps, using "ActiveCell" is causing the loop to occur, somehow?

I basically, just want the sub to run once per line, as the user moves through the document.

Thanks, in advance, for any help!

Aucun commentaire:

Enregistrer un commentaire