Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,739
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Aug 2014
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Code:
    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
    Last edited by ParamRay; Aug 13th, 2017 at 07:49 AM.

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,739
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you ParamRay,
    that works great

    Tony

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com