Allow N only if M has value

Topic

New Reply

How would I script to allow value to be entered in N only if corresponding M has a value, otherwise N would return text, “need M”. This text would appear only if I had tried to enter a value in N without a M value, otherwise N is blank.
M3, 4, 5 have values, therefore N3, 4, 5 would allow entry of values 10, 20, 30. But if I try to enter a value [40] in N6, it would not allow any value but return a text since M6 has no value/entry. If I enter value in M6, then value is allowed in N6.
N7 is blank because no entry was attempted. If M3 is deleted, then N3 returns text.
Solution must not allow this function to be overwritten when changing or adding values to M & N

Attached sample

Reply | Quote

Replies

Skipro,

Try this in a worksheet module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“N2:N100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” Then Target = “need M”
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range(“M2:M100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” Then Target.Offset(0, 1) = “need M”
        Application.EnableEvents = True
    End If
    
End Sub

HTH,
Maud

Reply | Quote

Maud,
Thank you, works fine.
I see you added to original script which I received via email. This addition corrects my 1st issue.
But when I add a value to M then delete it, the 2nd part of the script adds the text to N and it cannot be deleted.

Code:

If Not Intersect(Target, Range(“M2:M100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” Then Target.Offset(0, 1) = “need M”
        Application.EnableEvents = True

What I need is if no value exists in M I should be able to delete or change the value in N, whether it is the text returned from script or a manual entry, This was possible before the addendum of the 2nd part, though this is needed for the 1st issue which was an automatic refresh of N.

Reply | Quote

Skipro,

I think this solves it. The trick is to hold the previous value of the cell. This can be done by writing it to a another cell however, I chose to write it to a textbox on an unused form

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“N2:N100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” And Target  “” And UCase(UserForm1.TextBox1)  “NEED M” Then
            Target = “need M”
        End If
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range(“M2:M100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” Then Target.Offset(0, 1) = “need M”
        
        Application.EnableEvents = True
    End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    UserForm1.TextBox1 = Target
    On Error GoTo 0
End Sub

Reply | Quote

Hi Skipro/Maud

You only need the message ‘need M’ if you try and enter a value in N without having a corresponding non-blank entry in M. If you delete a value in M, you don’t need anything in N.

If you get the message ‘need M’, you are allowed to delete it.
But it will re-appear each and every time you try and enter a non-blank entry again in N without having a corresponding value in M.

The problem with using event-driven operations like this is that Users always try to find a way to circumvent this. Either intentionally, or unintentionally.
A common operation is ‘copying and pasting’ data from one location to another.
When you paste a block of cells, this triggers a ‘change’ event for every cell in the destination range.
This isn’t a problem if you deal with it properly, but you do need to have a clear head.

In my attached version, you can enter single cells as well as paste a block of cells, and I believe it gives the expected results.
I only check for changes detected in columns M and N. didn’t restrict the rows checked, but this can be easily added.

zeddy

Reply | Quote

Hi Skipro/Maud

The problem with using event-driven operations like this is that Users always try to find a way to circumvent this. Either intentionally, or unintentionally.

zeddy

This observation is why so much software does not work as desired. The developer knows what he wants to have happen and writes his code to make it so. When he tests his code, he (perhaps subconsciously) uses data that make it work. When users work with the software, they enter THEIR real data which may not have been tried at all by the developer.

Reply | Quote

zeddy,
Appears to work as needed.
After maud’s last revision, I thought that an automatic delete of N with delete of M was better than a text, which eventually needed to be deleted or overwritten. This was your approach, and for my needs, seems to works perfectly.
Thanks.

Reply | Quote

Hi nate..

What you say is true. But if you have a carefully defined checklist of what the software is required to do, and it then passes that checklist, you have delivered ‘what was asked for’. When subsequent ‘requests’ arise, it is usually the ‘originator’ that discovers their checklist does not encompass their ‘full’ requirement.
But, with quick delivery and turnround, you eventually arrive at the goal.
As far as using test data, a reasonable developer will have sufficient experience to test for most ‘realistic’ real-world expected data. But I would always include entering ‘banana’ where numeric values are expected etc etc to see what happens. For ‘extreme’ testing, the costs begin to mount. Sometimes it is more ‘cost-effective’ to train Users to ‘Don’t do that’.

zeddy

Reply | Quote

Hey Y’all,

It ‘s the old 90/10 rule. 90% of the requirements require 10% of the time and effort the last 10% of the requirements (often the a fore mentioned extreme testing/error trapping) consume the 90% of time and effort. This axiom has been around as long as I can remember and has proven true in my 35+ years of experience. YMMV :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote