Transferring data from one sheet to another based on cell drop down at the end of any given row.

[Rowland1986]

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.

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.

 

[Fluff]

Try adding this

''' CALCULATES NEXT BLANK ROW ON THE "ARCHIVE" WORKSHEET '''
            NxtRow = Sheets("Archive").Range("F" & Rows.Count).End(xlUp).row + 1
            [COLOR=#0000ff]If Range("F" & Target.row) = "" Then
               Target.Value = ""
               MsgBox "Column F is balnk"
               Exit Sub
            End If
[/COLOR]

 

[Rowland1986]

That worked perfectly, thank you very much.

 

[Fluff]

Glad to help & thanks for the feedback