Excel VBA Course
Excel VBA Course - From Beginner to Expert

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.)

View Course

Compelling Users to Enter Value in Fields

0

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

Answer
Discuss

Answers

0

Your new requirement can't, sensibly, be added to the code you have. However, there are two ways of achieving the result you desire. Both of them demand that you change the way you work.

  1. Create an entry form.
    That would be a user form in which you enter all the data required for one row. When the user clicks Save the data are transferred from the form to the worksheet. At that moment you can carry out a check as to whether the data are complete and admissible and refuse to save entries which are not.
    The drawback of this solution is that you would require rather elaborate coding to ensure that data aren't changed in the worksheet without calling the form and its checks. In fact, you can't exclude that possibility 100%.
  2. Create and check event.
    In VBA parlance an "event" is a particular occurrence that can be used to trigger the running of code. So far you are using the "Change" event which occurs when a cell value is changed. The same event might be used to check if SRV and SIV have been filled (in fact adding to your existing code) but for this to work you would have to have the working rule to fill these columns before entering an amount. Such a rule is sure to cause major problems resulting in endless error messages and interrupting work.
    An alternative would be to run a check on the last complete line when a few line is started. But, in the extreme, this might result in having the error message shown on the next day. Similar problems might arise if the check is carried out when the workbook is opened or saved or that particular worksheet activated.
    The point here is that you should "create" this event not by writing some code but by establishing rules in the work flow and then take advantage of those rules to write code that supportsd the flow insterad of disrupting it.
Discuss

Discussion

OK. Thank you. 
Whek (rep: 2) Jan 20, '18 at 2:05 am
Add to Discussion


Answer the Question

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