Selected Answer
Paste this code in the code sheet of the worksheet on which you have your columns for Debit, Credit and Balance. Then save your workbook as macro-enabled, in XLSM format.
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
At the top of the code you see the enumeration Nwc. That is where you can set the columns. A = 1, B = 2 etc. My code has Debit in column 3 (= C), Credit in column D and the Balance in column 5 (= E). DR/CR must be adjacent. The other columns can be anywhere.
The workflow I thought up is that the user would first input a date, then either a debit or credit amount. Now, please forget about your formula in the balance column. The macro calculates the balance from top (NwcFirstDataRow) to bottom (last row with the new date). If this balance is below zero the entry will be rejected. If it is higher it will be written in the balance column.
The user isn't allowed to write entries anywhere except in the row immediately below the last balance - not higher and not lower, either. If you need to induce a break, write a space (blank) in the balance column and you can use the next row after that.