Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Allow N only if M has value

    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
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
    Last edited by Maudibe; 2015-02-23 at 18:16.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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.
    Last edited by skipro; 2015-02-23 at 19:57.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
    Attached Files Attached Files
    Last edited by Maudibe; 2015-02-23 at 21:03.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    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
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    skipro (2015-02-25)

  7. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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.

  8. #7
    3 Star Lounger
    Join Date
    May 2010
    Location
    Philadelphia, PA, USA
    Posts
    208
    Thanks
    3
    Thanked 27 Times in 26 Posts
    Quote Originally Posted by zeddy View Post
    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.

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    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

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •