Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Conditional Validation and No Null Field requirements

    I have not been able to figure out how to conditionally require certain fields to not be null.

    Scenario:

    Column "F" contains "authPriv" then Columns G - J cannot be null
    Column "F" contains "authNoPriv" then Columns G and I cannot be null


    Any ideas/suggestions??

    Thanking you in advance,

    Bill

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    This type of verification can be a bit tricky. It's easy to check if the required cells are completed when the authPriv or authNoPriv are entered via a macro:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim lRow   As Long
       Dim lColPtr As Long
       
       If Target.Column = 6 Then
        
         lRow = Target.Row
         
         Select Case Target.Value
         
               Case "authPriv"
                   If WorksheetFunction.CountA(Range(Cells(lRow, ["G"]), Cells(lRow, ["J"]))) <> 4 Then
                     MsgBox "Privledge authPriv Requires Cols G-J to be completed!" & vbCrLf & _
                            vbCrLf & "Please Correct", _
                            vbCritical + vbOKOnly, _
                            "Data Consistency Error"
                     lColPtr = 7
                     Do
                       If Cells(lRow, lColPtr) = "" Then Cells(lRow, lColPtr).Select
                       lColPtr = lColPtr + 1
                     Loop Until (Cells(lRow, lColPtr - 1) = "")
                   End If
               Case "authNoPriv"
                   If (Cells(lRow, ["G"]).Value = "") Or _
                      (Cells(lRow, ["I"]).Value = "") Then
                     MsgBox "Privledge authPriv Requires Cols G and I to be completed!" & vbCrLf & _
                            vbCrLf & "Please Correct", _
                            vbCritical + vbOKOnly, _
                            "Data Consistency Error"
                     If (Cells(lRow, ["G"]).Value = "") Then
                       Cells(lRow, ["G"]).Select
                     Else
                       Cells(lRow, ["I"]).Select
                     End If
                   End If
                   
         End Select
         
       End If
    
    End Sub
    Note: The message is displayed then the cursor is located to the first cell missing data.

    However, what if the user deletes/blanks a required field afterwards? A macro could also handle this but it would get quite lenghty along the lines of what is above.

    Another approach is to use the macro above for initial checking and conditional formatting to highlight in red any missing data.

    This test sheet: BillF.xlsm

    shows that approach. Here you want to notice the difference in the Affected ranges of the formatting for the different conditions. Also that I only set the ranges for a few rows.

    HTH
    Last edited by RetiredGeek; 2015-09-16 at 20:52.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BillF426 (2015-09-16)

  4. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BillF426 View Post
    I have not been able to figure out how to conditionally require certain fields to not be null.

    Scenario:

    Column "F" contains "authPriv" then Columns G - J cannot be null
    Column "F" contains "authNoPriv" then Columns G and I cannot be null


    Any ideas/suggestions??

    Thanking you in advance,

    Bill
    Thank you for the reply. I haven't had time to check it out yet, but I think that a bit more info might also make a difference. Column "F" is a drop down with only 3 values. The first two have the requirements set above, the last places no requirements on the latter columns. Columns "G" and "H" are also drop downs with validated data. Columns "I" and "J" are text fields which will require info depending on the selection in column "F".

    I'm not sure if this makes any difference to the above answer or not. It will be later this afternoon or evening before I will have an opportunity try your fix and see how it works.

    Thank you again!

  5. #4
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you. This is resolved.

Tags for this Thread

Posting Permissions

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