Setting Threshold to Running Balance


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?  



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"
                .Cells(Rl, Target.Column).Select
                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"
                    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.



Thank you very much. I have adopted it and it works just fine.
Whek (rep: 2) Nov 9, '17 at 6:39 am
Perfect! It works just fine.
Whek (rep: 2) Nov 9, '17 at 9:47 am
I'm glad you got it working. Have a great day!
Variatus (rep: 813) Nov 9, '17 at 9:07 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login