How to Avoid MsgBox from Popping Up if 1st field the user encounters has nothing in the Target.Row F

For the "I don't want it to popup the MsgBox warning until the user enters data" part, move the code to the Worksheet_Change event.

Place the code for Sheet_change Event not for Selection_change Event.

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated

If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
   End If
End If
Application.EnableEvents = True
End Sub

Select allOpen in new window

What about clearing out whatever they put in that field based off the MsgBox warning?

Also, something strange happening.  Whenever the MsgBox warning pops up and I click OK, then it locks some of my cells.  Not sure what's going on there.

Another concern, there are 2 things setup under the Worksheet_Change, so sometimes depending on the circumstance, it wants to run both.

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
   End If
End If
Application.EnableEvents = True


' check to see if the DateAccepted2 is prior to the SubmittedDate
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 14 And Target.Row > 1 Then
   If Target <> "" Then
      If Target.Offset(0, -1) = "" Then
         Target = ""
         Application.EnableEvents = True
         Exit Sub
      End If
      If Not IsDate(Target) Then
         Application.EnableEvents = True
         Exit Sub
      Else
         If Target < Target.Offset(0, -1) Then
            MsgBox "DateAccepted cannot be prior to the SubmittedDate.", vbExclamation, "Incorrect Date!"
            Target = ""
            Target.Select
            Application.EnableEvents = True
            Exit Sub
         End If
      End If
   End If
End If
Application.EnableEvents = True

End Sub

Select allOpen in new window

Thanks,

gdunn59

Try this.....

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
   End If
' check to see if the DateAccepted2 is prior to the SubmittedDate
ElseIf Target.Column = 14 And Target.Row > 7 Then
   If Target <> "" Then
      If Target.Offset(0, -1) = "" Then
         Target = ""
         Application.EnableEvents = True
         Exit Sub
      End If
      If Not IsDate(Target) Then
         Application.EnableEvents = True
         Exit Sub
      Else
         If Target < Target.Offset(0, -1) Then
            MsgBox "DateAccepted cannot be prior to the SubmittedDate.", vbExclamation, "Incorrect Date!"
            Target = ""
            Target.Select
            Application.EnableEvents = True
            Exit Sub
         End If
      End If
   End If
End If
Application.EnableEvents = True
End Sub

Select allOpen in new window

sktneer,

That seemed to work.  The only thing is that Column 14 also needs to be checked against if there is any data in Target.Row "F".

But I guess if Column 13 is checked for it the code automatically clears out Column 13 and moves to the next row, and if the user goes back to try and enter something in Column 14, it just wipes it out (am I correct on how this is working)?

Thanks,

gdunn59

Okay try it like this...

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
   End If
End If
' check to see if the DateAccepted2 is prior to the SubmittedDate
If Target.Column = 14 And Target.Row > 7 Then
   If Target <> "" Then
      If Target.Offset(0, -1) = "" Then
         Target = ""
         Application.EnableEvents = True
         Exit Sub
      End If
      If Not IsDate(Target) Then
         Application.EnableEvents = True
         Exit Sub
      Else
         If Target < Target.Offset(0, -1) Then
            MsgBox "DateAccepted cannot be prior to the SubmittedDate.", vbExclamation, "Incorrect Date!"
            Target = ""
            Target.Select
            Application.EnableEvents = True
            Exit Sub
         End If
      End If
   End If
End If
Application.EnableEvents = True
End Sub

Select allOpen in new window

sktneer,

Your last posting is working, the only thing is when it checks for data in Target.Row "F" and wipes out what date the user puts there it moves to the next row which is fine, but then the Cell M13 and M14 become locked.

Is it supposed to be doing that?

Thanks,

gdunn59

but then the Cell M13 and M14 become locked.

What do you mean by that?

sktneer,

What I meant is if that statement is true, then it locks that cell.

I tried your last solution but the cells are still getting locked.

Here is the Code for that:

Select allOpen in new window

One more thing.  when the statement evaluates to true for Column M (there is nothing in Target.Row "F"), after the user clicks on OK in the MsgBox, the cursor moves to the next row in Column M.  I would need this to also do the same if the statement is true for Column N, then the cursor would need to move to the next row for Column M.  This should only happen if that statement evaluates to True.

One other issue I've run into (and I may have to open another question for this), is in my code that does the data refresh I have it so it unprotects the sheet to do the data refresh, and then once the refresh is complete, it protects the sheet again.  The only problem with this is a user can click on Review, Protect Sheet, and it will allow them to unprotect the sheet if they want to, which I don't want them to be able to do this.  Is there a way around this?

Here is the code I have for the "unable to update SubmittedDate . . . " , etc., and further down in the code is the REFRESH CODE.

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
      Application.EnableEvents = True
   End If
End If
' check to see if the DateAccepted2 is prior to the SubmittedDate
If Target.Column = 14 And Target.Row > 7 Then
   If Target <> "" Then
      If Target.Offset(0, -1) = "" Then
         Target = ""
         Application.EnableEvents = True
         Exit Sub
      End If
      If Not IsDate(Target) Then
         Application.EnableEvents = True
         Exit Sub
      Else
         If Target < Target.Offset(0, -1) Then
            MsgBox "DateAccepted cannot be prior to the SubmittedDate.", vbExclamation, "Incorrect Date!"
            Target = ""
            Target.Select
            Application.EnableEvents = True
            Exit Sub
         End If
      End If
      Target.Offset(0, 1).Select
   End If
End If

Application.EnableEvents = True

End Sub







The REFRESH CODE
Private Sub cmdRefreshSQLData3ed_Click()
' RefresheBillDatafromSQLSP Macro
' RefresheBillDatafromSQLSP
'
Dim i As Long
Dim wb1 As Excel.Workbook
Dim k As Long

ActiveWorkbook.Worksheets("E-Bill Tracking").Activate
ActiveWorkbook.ActiveSheet.Unprotect Password:="eBillStatus"

i = 8
k = 2


Dim ctrl As CommandBarControl
Dim cb As CommandBar
Application.CommandBars(1).Controls("Tools").Controls("Protection").Enabled = False
     
' Warn user that existing data in Columns M, N and O will be deleted
YesNo = MsgBox("Any existing data in SubmittedDate, DateAccepted2 and Comments2 will be deleted.  Do you wish to proceed?", vbYesNo + vbCritical, "WARNING!")
    If YesNo = vbNo Then
        Range("A8").Select
        ActiveSheet.Protect
        ActiveSheet.Protect AllowFiltering:=True
        Exit Sub
    Else
        'remove any filters prior to refreshing the data
        If ActiveWorkbook.ActiveSheet.FilterMode Or _
        ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
        ActiveWorkbook.ActiveSheet.ShowAllData
        Range("A8").Select
        
' Remove any existing data in Columns M, N and O
Do Until i > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Cells(i, 13).Value <> "" Or Cells(i, 14).Value <> "" Or Cells(i, 15).Value <> "" Then
        Cells(i, 13).Value = ""
        Cells(i, 14).Value = ""
        Cells(i, 15).Value = ""
        
        k = k + 1
        
    End If
    
    i = i + 1
Loop


For Each objconnection In ThisWorkbook.Connections
    'Get current background-refresh value
    bBackground = objconnection.OLEDBConnection.BackgroundQuery

    'Temporarily disable background-refresh
    objconnection.OLEDBConnection.BackgroundQuery = False

    'Refresh this connection
    objconnection.Refresh

    'Set background-refresh value back to original value
    objconnection.OLEDBConnection.BackgroundQuery = bBackground
Next

End If

Range("L8").Select

ActiveWorkbook.ActiveSheet.Protect
ActiveSheet.Protect AllowFiltering:=True

End Sub

Select allOpen in new window

Thanks,

gdunn

In the second IF statement (only for column N i.e. Target.Column=14), change the following line of code..
Target.Select

TO
Target.Offset(1).Select

This will select the next cell down the row.

If you have applied protection to the sheet, it depends what columns or cells are unlocked for accepting the data entry. That is a different question altogether.

sktneer,

The second IF statement (only for column N i.e. Target.Column=14, is the wrong statement.

I'm referring to the statement above that:

' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
      Application.EnableEvents = True
   End If
End If

Select allOpen in new window

Well I am clearly missing your question here.

Target means the cell which is being changed.
Target.Select means select the target itself.
Target.Offset(1,0) means select the one cell below the Target cell.

Based on above description, make changes in the code as per your requirement.

sktneer,

What I'm saying is you told me to change the following:

Target.Select

 TO
 Target.Offset(1).Select

The only code that has that is the 2nd If Statement, but that isn't the one that I need the change on.

I need it to move the cursor after it's done evaluating to true, in the 1st If Statement (see attached), but it only needs to pertain to Column 14.

Hope this makes more sense.

Thanks,

gdunn59
Document1.docx

Still unable to get you.

Once you get the msg "You cannot update SubmitDate" for the first IF statement, where do you want the cursor to move, one cell down or to remain in the same cell?
Talk in terms of cell references, that will be more clear to understand your requirement.

Also did you read the Post ID: 41518401?
It is so easy to change it once this concept is clear.

Still having the issues I posted in ID: 41516667  where it is locking the cells after lines of code 3-11 evaluate to true.  So for example, I deleted the value that was in Cell F8, which since there is nothing there and a user tries to enter a date in the M8 or N8 or comments in O8, then the MsgBox prompt would pop up, and once I click on OK to clear the MsgBox, any of those 3 fields that I attempted to enter data into and got the MsgBox prompt, I can no longer access those fields, they are locked.

Why would this be happening????

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
' unable to update SubmittedDate, DateAccepted2 or Comments2 because no eBill has been generated
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" And Len(Cells(Target.Row, "A")) <> 0 Then
      MsgBox "You cannot update SubmittedDate, DateAccepted2 or Comments2 because this Invoice has not had an eBill file generated yet.", vbExclamation, "InvElecHistoryID Missing!"
      Target.Clear
      Target.Offset(1).Select
      Application.EnableEvents = True
   End If
End If
' check to see if the DateAccepted2 is prior to the SubmittedDate
If Target.Column = 14 And Target.Row > 7 Then
   If Target <> "" Then
      If Target.Offset(0, -1) = "" Then
         Target = ""
         Application.EnableEvents = True
         Exit Sub
      End If
      If Not IsDate(Target) Then
         Application.EnableEvents = True
         Exit Sub
      Else
         If Target < Target.Offset(0, -1) Then
            MsgBox "DateAccepted cannot be prior to the SubmittedDate.", vbExclamation, "Incorrect Date!"
            Target = ""
            Target.Select
            Application.EnableEvents = True
            Exit Sub
         End If
      End If
      Target.Offset(0, 1).Select
   End If
End If

Application.EnableEvents = True

End Sub

Select allOpen in new window

As per the code if a cell in col. A is not blank and if cell in col. F is blank, user won't be allowed to input a value in corresponding cell in col. M or col. N or col. O.
In other words, user is supposed to fill the cell in col. F first if cell. in col. A is not empty in order to fill a date in col. M or N or O. That is the purpose of the code.

e.g. take example of Row8

If A8 is not empty and F8 is blank, user cannot populate the cells M8, N8 or O8.

Now take the example of Row9.
If A8 is not empty and F9 is not empty, user can populate the cells M9, N9 and O9.

And in both the above cases, the code will be triggered whenever user tries to edit the cell either in col. M, or N or O.