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