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: 823) Nov 9, '17 at 9:07 pm
I have successfully adopted the code above but need to add one more function. 
Here, I have two additional columns namely SIV and SRV. Both columns are designed to accept numeric and alphanumeric values and are associated with DEBIT and CREDIT. Both should not be null. Users should be compelled to enter the SIV value before they can enter the Debit value. Similarly, the SRV value must be entered before Credit value is allowed. If users try to enter Debit value without entering the SIV value, they receive error message and the operation reversed. The same applies for Credit value. The SRV value must be entered before the Credit value is allowed. How can I achieve that? Thank you in advance.
Whek (rep: 2) Jan 18, '18 at 3:36 pm
Obviously, that is a new question. Knowing what your code was like before you adapted it will not help. Start this question from scratch, I suggest, and that includes showing your own effort in a new thread.
Variatus (rep: 823) Jan 18, '18 at 6:58 pm
Add to Discussion

Answer the Question

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