Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
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:
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!
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:
VBA 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!