I have the following code that works just fine but need to add further function.
I have added two more columns: SIV and SRV. the SIV is associated with DEBIT and the SRV is associated with CREDIT. I would like to compel users to enter the SRV before CREDIT value is entered. Similarly, users must enter the SIV value before DEBIT is entered. If a user fails to enter either SIV or SRV, s/he receives an error compelling him/her to enter the value.
How do I achieve that and add that to the code below?
Option Explicit
Private Enum Nwc ' Worksheet columns
' 27 Oct 2017 ' assign any integer value to any enumeration.
' Enumerations without assigned value
' are incremented by 1 from the one above
NwcDate = 1 ' used to find the last used row
' DR and CR must be adjacent!
NwcDR = 3 ' 3 = column C
NwcCR ' [auto] = 4 = D
NwcBal ' [auto] = 5 =
NwcFirstRow = 2 ' rows above NwcFirstRow are ignored
' change as required by your worksheet design
End Enum
Private Sub Worksheet_Change(ByVal Target As Range)
' 28 Oct 2017
Dim Rl As Long
Dim Rng As Range
Dim Bal As Double
Rl = Cells(Rows.Count, NwcDate).End(xlUp).Row
Set Rng = Range(Cells(NwcFirstRow, NwcDR), Cells(Rl, NwcCR))
With Application
.EnableEvents = False
If Not .Intersect(Target, Rng) Is Nothing Then
Rl = Cells(Rows.Count, NwcBal).End(xlUp).Row + 1
If Target.Row <> Rl Then
' the only allowed row is the one immediately under
' the last shown balance:
MsgBox "Past entries may not be modified." & vbCr & _
"Please make your entry in the next" & vbCr & _
"empty row.", _
vbCritical, "Unauthorised overdraft"
.Undo
.Cells(Rl, Target.Column).Select
Else
Bal = .Sum(Rng.Columns(2)) - .Sum(Rng.Columns(1))
If Bal < 0 Then
MsgBox "This debit would create a negative " & vbCr & _
"balance of " & Bal & " which is not permitted." & vbCr & _
"The entry will be removed.", _
vbExclamation, "Unauthorised overdraft"
.Undo
Else
Cells(Rl, NwcBal).Value = Bal
End If
End If
End If
.EnableEvents = True
End With
End Sub