Required cell

[karmakat]

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??

 

[Joe4]

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.

 

[karmakat]

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.

 

[Joe4]

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)?

 

[karmakat]

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)

 

[Joe4]

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.

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

 

[karmakat]

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.

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!!

 

[Joe4]

You are welcome.

 

[karmakat]

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?

 

[Joe4]

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?