Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Required cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Required cell

    ok, I've tried about 2 dozen methods to acheive this, so I'm putting up a post to see if anyone here can help me.

    Here's what I'm trying to acheive:

    I have a sheet with 10 columns Date Served, Last Initial, First initial, Zip, First Time Visit, Current month discount, over 60, Men, Women, Children. it is imperative that I have Zip entered. I'm looking for a way to stop the user from proceeding to the next cell "First time Visit", if "Zip" is left blank. preferably this would happen when the attempt to leave the cell, but I can live with preventing the file from being closed with empty cells.


    Help??

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Required cell

    Welcome to the Board!

    Usually, one of the best way to do this is to not allow them to enter the data directly on the spreadsheet, and instead create a User Form for data entry, and not allow them to submit the data until that field on the form is populated. So, once they have satisfied populating all the required fields on the Form, the Submit button on the Form will write the data to the spreadsheet.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Required cell

    Quote Originally Posted by Joe4 View Post
    Welcome to the Board!

    Usually, one of the best way to do this is to not allow them to enter the data directly on the spreadsheet, and instead create a User Form for data entry, and not allow them to submit the data until that field on the form is populated. So, once they have satisfied populating all the required fields on the Form, the Submit button on the Form will write the data to the spreadsheet.
    Yeah, in this case a form won't work- its a side effect of the audience that will be using the spreadsheet. I'm making changes to a file that has been in use for over a decade, with specific instructions to keep the appearance of the file the same.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Required cell

    Yeah, in this case a form won't work- its a side effect of the audience that will be using the spreadsheet. I'm making changes to a file that has been in use for over a decade, with specific instructions to keep the appearance of the file the same.
    Yeah, unfortunately I am familiar with some of the antiquated, change-resistant thinking many decision makers have. It is one of the reasons we were "stuck" using old, outdated software in some of my previous jobs, and kept us from growing and being taking advantage of new technologies to give out customers a better experience, but that I digress...

    So, let's see what we can do with what you are stuck working with.
    What columns does the Zip Code and First Time Zip show up in?
    Does your data start on row 2 (assuming that titles in row 1)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Required cell

    Quote Originally Posted by Joe4 View Post
    Yeah, unfortunately I am familiar with some of the antiquated, change-resistant thinking many decision makers have. It is one of the reasons we were "stuck" using old, outdated software in some of my previous jobs, and kept us from growing and being taking advantage of new technologies to give out customers a better experience, but that I digress...

    So, let's see what we can do with what you are stuck working with.
    What columns does the Zip Code and First Time Zip show up in?
    Does your data start on row 2 (assuming that titles in row 1)?
    This is (thankfully) the last version of this file that will ever go out- starting the first of the year everyone will be entering their data directly into a SharePoint list.

    I do have hearders in row 1, and the columns listed start on the left with 1, so the first zip goes into (4,2)

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Required cell

    Try the following code. Just right-click on the sheet tab name at the bottom of your screen, select View Code, and paste this code in the resulting VB Editor window.
    This will not allow you to enter anything in column E in any row if column D in that row is blank.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
        
    '   See if updates cell in column E
        Set rng = Intersect(Target, Columns("E:E"))
        If rng Is Nothing Then Exit Sub
        
    '   Loop through updated cells in column E
        For Each cell In rng
    '       Exclude title row
            If cell.Row > 1 Then
    '           Check to see if column D has a value
                If cell <> "" And Len(cell.Offset(0, -1)) = 0 Then
                    Application.EnableEvents = False
                    cell.ClearContents
                    Application.EnableEvents = True
                    MsgBox "You must populate column D before column E", vbOKOnly, "ENTRY ERROR!"
                End If
            End If
        Next cell
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Required cell

    Quote Originally Posted by Joe4 View Post
    Try the following code. Just right-click on the sheet tab name at the bottom of your screen, select View Code, and paste this code in the resulting VB Editor window.
    This will not allow you to enter anything in column E in any row if column D in that row is blank.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
        
    '   See if updates cell in column E
        Set rng = Intersect(Target, Columns("E:E"))
        If rng Is Nothing Then Exit Sub
        
    '   Loop through updated cells in column E
        For Each cell In rng
    '       Exclude title row
            If cell.Row > 1 Then
    '           Check to see if column D has a value
                If cell <> "" And Len(cell.Offset(0, -1)) = 0 Then
                    Application.EnableEvents = False
                    cell.ClearContents
                    Application.EnableEvents = True
                    MsgBox "You must populate column D before column E", vbOKOnly, "ENTRY ERROR!"
                End If
            End If
        Next cell
        
    End Sub
    That works perfectly! THANK YOU!!

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Required cell

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Required cell

    So, on my way out of the office tonight the user requesting this spreadsheet stopped me to say "what I actually want to do is prevent exirting if there are empty fields in either zip or date served"

    ugh.........any pointers?

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Required cell

    You can add code to the "BeforeClose" workbook event, which triggers to run automatically whenever they try to close the workbook. Is that what you are looking for?

    If so, we need to determine which rows actually have data and need to be checked. For rows with data, is there one column that is ALWAYS populated, so we know we can look at that column to determine exactly where our data ends?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

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
  •