200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have three columns A, B and C.
Column A holds credit. Column B holds debit while column C holds the running balance.
I wish to setup a threshold such that once the running balance is zero, no further entry should be allowed into the debit column until credit is increased. Put differently, no negative running balance should be allowed.
How do I achieve this?
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.