Add message box

[Rob_010101]

Hello,

The below macro currently works perfectly and moves a line of data based on the selection from a drop-down menu (data validation list).

I'd like to add a simple "are you sure you want to proceed" "yes" "no" style button message box when the user selects the item.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 On Error Resume Next
    Sheet1.ShowAllData
  On Error GoTo 0
 
  Dim fromRow%
  Dim archiveRow%
  Dim strMatch As String
  Dim wsTarget As Worksheet
  Dim blnMove As Boolean
  Dim blnOnlyValues As Boolean
  
  If Target.Cells.Count > 1 Then Exit Sub

  If Not Application.Intersect(Target, Range("H7:H2000")) Is Nothing Then
    blnOnlyValues = False
    Select Case UCase(Target.Value)
      Case "COMPLETE"
        Set wsTarget = ThisWorkbook.Worksheets("Completed")
        blnMove = True
        blnOnlyValues = True
          End Select
    If blnMove Then
      fromRow = ActiveCell.Row
      With wsTarget
        If .FilterMode Then
          strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
          archiveRow = Evaluate(strMatch) + 1
        Else
          archiveRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(3).Row + 1
        End If
      End With
Range(Cells(fromRow, 1), Cells(fromRow, 8)).Copy wsTarget.Cells(archiveRow, 1)
With wsTarget
    .Range(.Cells(archiveRow, 1), .Cells(archiveRow, 1)).FormatConditions.Delete
End With
If blnOnlyValues Then wsTarget.Cells(archiveRow, 1).Resize(1, 20).Value = Cells(fromRow, 1).Resize(1, 20).Value
Application.EnableEvents = False
Rows(fromRow).EntireRow.Delete
Application.EnableEvents = True
Set wsTarget = Nothing
    End If
  End If

End Sub

I should add, I have this code

ans = MsgBox("are you sure you want to continue ?", vbYesNo)
If ans = vbNo Then Exit Sub

But I need to be able to move the line back from the other sheet which I have used the same code for but in reverse if a user does this incorrectly and this causes strange behavior

 

[rlv01]

The code you have :

ans = MsgBox("are you sure you want to continue ?", vbYesNo)
If ans = vbNo Then Exit Sub

Seems fine for that purpose.

 

[Joe4]

If you put this confirmation at the beginning of the code before the meat of the code runs instead of "after the fact", then you shouldn't need to reverse anything.

 

[Rob_010101]

Thanks guys, looks like I had it added in the wrong place!

 

[Joe4]

You are welcome.
Glad we were able to help!