Rowland1986
New Member
- Joined
- May 15, 2018
- Messages
- 8
I am currently looking over some code that was created by a staff member that has now left. The code is basic and works okay for what we need, however, there is one piece I would like to add in but cannot work out how do it.
Currently what the code does is anytime someone chooses "yes" from the drop down at the end of any given row, a confirmation message box appears. By clicking "ok" the entire row transfers to a different sheet in the same workbook. Clicking cancel, removes the word "yes" and then does nothing.
What I am looking to add is, before running the code for the "ok" option, I would like it to check column "F" on the row that is currently selected and return a message box if that cell has been left blank and also cancel the transfer action until this cell in column "F" has been completed.
Any help with this issue would be greatly appreciated.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
''' IDENTIFIES IF A CELL IN COLUMN "Q" IS SHOWING AS "YES" '''
If Target.Column = 17 Then
If Target = "Yes" Then
Dim Ans As Integer
Dim NxtRow As Integer
''' CALCULATES NEXT BLANK ROW ON THE "ARCHIVE" WORKSHEET '''
NxtRow = Sheets("Archive").Range("F" & Rows.Count).End(xlUp).Row + 1
''' CALLS A MESSAGE BOX ASKING FOR ARCHIVE CONFIRMATION '''
Ans = MsgBox("Are You Sure You Have Completed This Case? Pressing YES Will Archive This Work!", vbOKCancel)
''' TRANSFERS ENTIRE ROW OF DATA FROM WORKSHEET TO ARCHIVE IF "OK" IS CLICKED IN THE MESSAGE BOX '''
Select Case Ans
Case vbOK
Application.EnableEvents = False
ThisWorkbook.Save
Target.EntireRow.Copy _
Destination:=Sheets("Archive").Range("A" & NxtRow)
ThisWorkbook.Save
Target.EntireRow.Delete
Application.EnableEvents = True
''' REMOVES "YES" FROM CELL IN COLUMN "Q" AND CANCELS TRANSFER OF DATA TO THE "ARCHIVE" WORKSHEET IF "CANCEL" IS CLICKED IN THE MESSAGE BOX '''
Case vbCancel
Selection.ClearContents
Exit Sub
End Select
End If
End If
Application.ScreenUpdating = True
End Sub
Currently what the code does is anytime someone chooses "yes" from the drop down at the end of any given row, a confirmation message box appears. By clicking "ok" the entire row transfers to a different sheet in the same workbook. Clicking cancel, removes the word "yes" and then does nothing.
What I am looking to add is, before running the code for the "ok" option, I would like it to check column "F" on the row that is currently selected and return a message box if that cell has been left blank and also cancel the transfer action until this cell in column "F" has been completed.
Any help with this issue would be greatly appreciated.