VBA to bring up message box and add data if the answer is yes

[tonywatsonhelp]

Hi Everyone,

I have a tab call "Sales"

In Column A in have statuses,

In column J I have if the job is current or completed.

I'd like a macro that could do this,

If a cell in column A is changed to Active then that same row Column J = "Current"
if a cell in column A is Changed to Inactive then I get a message box saying "Is Sale (and the name of the sale from Column D of the same row) Completed?

if they answer Yes then Column J same row = "Completed"
if they answer No "Current"

anything else no change.

Please help if you can

Thanks

Tony

 

[ParamRay]

Hello, try this. It goes in the code module for the "sales" sheet.
Also, it assumes that row 1 contains the column headers, with the actual records commencing in row 2.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngResult As VbMsgBoxResult
  Dim rngProgress As Range
  Dim rngStatuses As Range
  Dim rngStatus As Range
  Dim rngName As Range
  
  Set rngStatuses = Intersect(Target, Me.Range("A2:A" & Me.Rows.Count))
  
  If Not rngStatuses Is Nothing Then
    For Each rngStatus In rngStatuses
      Set rngProgress = Intersect(rngStatus.EntireRow, Me.Columns("J"))
      Set rngName = Intersect(rngStatus.EntireRow, Me.Columns("D"))
      
      Select Case rngStatus.Value
        Case "Active": rngProgress.Value = "Current"
        Case "Inactive":
          lngResult = MsgBox("Is the sale '" & rngName.Value & "'completed?", vbQuestion + vbYesNo)
          If lngResult = vbYes Then
            rngProgress.Value = "Completed"
          Else
            rngProgress.Value = "Current"
          End If
        Case Else: 'do nothing
      End Select
    Next rngStatus
  End If
End Sub

 

[tonywatsonhelp]

Thank you ParamRay,
that works great

Tony