Stop data entry into cell with condition
Stop data entry into cell with condition
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Stop data entry into cell with condition

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi,
    I would like to stop anybody to enter anything into B1 if A1 does not have anything in it. If A1 has an entry then I should be able to enter data into B1.

    How do I achieve this.

    Thanks for your help in advance
    Andonny

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    why do you want to do it?
    can you run a macro at the end that says if a cell i blank that put in a certain value?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    Use a custom validation with this formula=OFFSET(B1,0,-1)<>""

    Put this in the Error Alert
    "Entry is only allowed when A1 is not blank"
    Regards
    Derek

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I am not too sure where and how to put the alert

    Thanks a million
    Andonny

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    From the Data Validation window, click on the Error Alert tab

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    Great this works perfectly. Is it also possible to stop an entry all together. This way it is still possible to make an entry if the alert is ignored. I am happy with this solution but just in case somebody wants to take it a step further.

    That would be down the the whole column B.

    Your help is very much appreciated
    Andonny

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again
    In data validation, on the ERROR ALERT tab you need a tick in the box against "Show error alert after invalid data is entered" and in the Style drop down box you need to select STOP. This will prevent an entry succeeding.

    With data validatiion already in B1 just select column B, go to data validation and go OK to extending the validation to other cells.

    Be aware, however, that validation is a type of cell formatting and that it can be circumvented by copying a non-validated cell and pasting it over your validated cell.

    regards
    Derek

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andonny

    An alternative to data validation is an event macro.

    Right click your sheet name tab, left click View Code and paste this code into the white area. Use Alt+F11 to return to your sheet.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Column = 2 Then
    If Target.Offset(0, -1) = "" Then
    MsgBox "You cannot enter data in Column B if the cell in Column A is blank"
    Target.Offset(0, 1).Select
    End If
    End If
    End Sub

    You will get fewer unnecessary messages if you also go to Tools, Options, Edit and change the setting of "Move selection after Entry" so that the Direction is Right (rather than Down)

    Good Luck
    Derek

  9. #9
    New Member
    Join Date
    Dec 2013
    Location
    Seattle
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stop data entry into cell with condition

      
    Hi Derek,

    I am trying to perform this same task but with the added condition that cell B1 must be populated with specified text in addition to cell A1 being empty. Using the word 'Empty' as an example of the text that must be displayed byB1, how would I update the VBA to capture this added condition?

    Thanks!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com