+ Reply to Thread
Results 1 to 7 of 7

Check if any value in ColB, then Allow entry in that Row, Else Not to be Allowed

Hybrid View

  1. #1
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Check if any value in ColB, then Allow entry in that Row, Else Not to be Allowed

    Dear Friends,

    I don't want to allow the user to enter any value in any other column if any value is not there in ColB.

    For example, if B15 is Empty, then in the entire row the user should not be allowed to enter any value. Hence the colB should be the key value column and based on the same only other entries should be allowed.

    Thanks in advance.

    acsishere.
    Last edited by VBA Noob; 07-24-2008 at 05:00 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    First highlight your range/table.

    In Data Validation, Select Custom and unclick the "Ignore Blanks" then in the custom box,
    =$B2<>""
    assuming you began highlighting in row 2.

    Does this work for you?

    ChemistB

  3. #3
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Smile Dear Sir,

    Thanks for your speedy response.

    I have a data range B:Z. Within this range in few columns ALREADY DATAVALIDATION is applied. Is it possible to apply once again datavalidation on the same?

    Else, any other solution? any macro will do?

    Thanks in advance,

    acsishere.
    Last edited by acsishere; 07-24-2008 at 02:17 PM.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    It is possible depending on what data validation you have. For example, lets say you already have Column C data validation such that C>5. Your formula for Column C (starting in row 2) Custom =AND(C2>5, $B2<>"")

    I'm sorry, I wouldn't know how to do it with a macro.

    ChemistB

  5. #5
    Registered User
    Join Date
    04-15-2008
    Location
    Tamil Nadu, India.
    MS-Off Ver
    Microsoft Office 2016
    Posts
    584

    Smile Dear Sir,

    Again, thanks for your reply.

    I have applied data validation in certain columns, something like (in list: =SUBJECT (it's a named range).

    If it is so, how to apply again the data validation for entire row other than ColB, to avoid entry in other columns of that entire row.

    I think as the possibility is there to enter anywhere in the sheet, I hope the macro only will solve the problem.

    Any help is highly appreciated.

    Thanks in advance.

    acsishere.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    Place this code within your worksheet module (Right click on the worksheet tab and "View Code")
    Private Sub Worksheet_Change(ByVal target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrTrap
    If target.Column > 2 Then
        i = target.Row
        If Cells(i, 2).Value = "" Then
            MsgBox ("A value must be entered into Column B first")
            target.Value = ""
        End If
    End If
    ErrTrap:
    Application.EnableEvents = True
    End Sub
    Let me know if it works.

    ChemistB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1