Is My Use of CANCEL What I Need To Return My Original Value To A Changed Cell

[Ark68]

So, I'm inthe midst of tackling a problem with my worksheet change event code.
When the user changes the value of a cell via that cell's dropdown (cell validation), the worksheet change event triggers some code for error checking. If the condition exists where the change is invalid, I wish for that cell's value to return to the orginal value before it had been changed. I'm thinking that CANCEL might be the route to go, but with my novice understanding of CANCEL, this may be totally inappropriate. Here's my abridged code:

Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)
    With ws_dsched      
       If Target.Address = "$N$18" Then
            src_r = Target.Row
            e1 = ws_roster.Cells(src_r, 8)
            u = .Range("R18").Value
            pstn = .Range("N18") & u
            r_pstn = Application.WorksheetFunction.Match(pstn, ws_roster.Columns(7), 0)
            If ws_roster.Cells(r_pstn, 3) = "Vacancy" Then
                ui1 = MsgBox("This position is vacant." & Chr(13) & "Do you wish to reassign " & e1 & " to position: " & pstn & "?", vbQuestion + vbYesNo, "REASSIGN EMPLOYEE")
                If ui1 = vbNo Then
                    Cancel = True
                    Exit Sub
                End If
                Stop
            Else
                e2 = ws_roster.Cells(r_pstn, 3)
                ui1 = MsgBox("This position is held by " & e2 & Chr(13) & "Do you wish to reassign " & e1 & " to position: " & pstn & "?" & Chr(13) _
                    & "This will orphan " & e2 & ".", vbQuestion + vbYesNo, "REASSIGN EMPLOYEE")
                If ui1 = vbNo Then
                    Cancel = True
                    Exit Sub
                End If
                Stop
            End If
        End If
    End With

My use of CANCEL is returning a "Procedure declaration does not match description of event or procedure having the same name." I'm unsure what that means, let alone correct it. It I am using CANCEL inappropriately for what I am trying to ultimately do, I am open for suggestions.

Thanks you!

 

[rlv01]

I think you are getting that error message because you have added the Cancel parameter. The procedure declaration for the Worksheet change event is:

Worksheet_Change(ByVal Target As Range)

End Sub

There is no Cancel parameter for the Worksheet_Change event subroutine , so the error message is telling you there is something wrong with the procedure declaration.

 

[Ark68]

Thank you rlv01. So, using CANCEL in this situation will not work.
I will take any advice to how I can somehow return the original value to the changed cell? Would the only way be to retain the values elsewhere (like a holding worksheet) and call on it to replace the incorrect value in that respective cell? Wondering if there is a special function or something.

 

[rlv01]

One way is to create a hidden sheet to preserve certain values that you might want to revert to.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("Sheet2")        'hidden worksheet

    If Target.Address = "$N$18" Then
        Select Case MsgBox("Cell value (" & Target.Address & ")  = " & Target.Value & vbCr & vbCr & "Yes = Keep" & vbCrLf _
                         & "No = Revert to previous value", vbYesNo Or vbQuestion, Application.Name)
        Case vbYes
            WS.Range("$N$18").Value = Target.Value 'preserve value on hidden sheet
        Case vbNo
            Application.EnableEvents = False
            Target.Value = WS.Range("$N$18").Value   'restore prev value from hidden sheet
            Application.EnableEvents = True
        End Select
    End If
End Sub

 

[Ark68]

Thanks rlv01, I figured that was the way to go. Does the code you provided somewhere put the original value on the hidden worksheet? All I can see is where it is retrieved from it. Thats the issue I'm facing is at what point would I send the original values to the hidden worksheet.

 

[rlv01]

Thats the issue I'm facing is at what point would I send the original values to the hidden worksheet.

That would be a function of your code design. Only you understand the circumstances of when you want to preserve the original values on the hidden worksheet ("Sheet2" in my example. )

 

[Rick Rothstein]

I'm about to go to sleep, so I don't have the time to set up a test scenario, but I am thinking that using...

Application.Undo

wherever you have...

Cancel = True

might work for you.

 

[Ark68]

Thank you. I'm working away at. I found a less than stellar solution to get me through, but I will give your solution a shot Rick. Much appreciated!!