Using VBA to add a name to a cell

[deftincu]

Hi All,

I have the code below, which currently inputs a timestamp when a status containing the words “On Hold” is chosen from a drop-down list in column Z, as well as a timestamp when “Assigned” is chosen. When “Reset…” is chosen it clears the cells. I am now trying to figure out how to add a name to an adjacent cell, based on the name that’s appearing in the status. For example; “On Hold - Chris”, I would like it to automatically input “Chris” in the adjacent cell. There will be multiple names I need this for, but each will be based on that same type of format: “On Hold - Sara”, “On Hold - Joe”, etc.

I tried adding it into the code, as you can see in the highlighted section below, but it’s not working. Any suggestions?

 

[Skyybot]

Where is the "Name" data stored?

 

[DanteAmor]

If you modify only one cell at a time, then a For each R is not necessary, so it could be:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
      Select Case True
        Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
          With Target.Offset(0, 2)
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End With
          If InStr(1, Target.Value, "-") > 0 Then
            Target.Offset(0, 15).Value = Trim(Split(Target.Value, "-")(1))
          End If
          
        Case Target.Value = "Assigned"
          Target.Offset(0, 10).Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
          
        Case Target.Value = "Reset Hold Date"
          Target.Offset(0, 2).Value = ""
          
        Case Target.Value = "Reset Assigned Date"
          Target.Offset(0, 10).Value = ""
          
      End Select
  End If
End Sub

But if you are going to modify several cells at the same time, then use the following:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Range("Z:Z"))
  If Not rng Is Nothing Then
    For Each c In rng
      Select Case True
        Case InStr(1, c.Value, "On Hold", vbTextCompare) > 0
          With c.Offset(0, 2)
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End With
          If InStr(1, c.Value, "-") > 0 Then
            c.Offset(0, 15).Value = Trim(Split(c.Value, "-")(1))
          End If
          
        Case c.Value = "Assigned"
          c.Offset(0, 10).Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
          
        Case c.Value = "Reset Hold Date"
          c.Offset(0, 2).Value = ""
          
        Case c.Value = "Reset Assigned Date"
          c.Offset(0, 10).Value = ""
          
      End Select
    Next
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[deftincu]

If you modify only one cell at a time, then a For each R is not necessary, so it could be:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
      Select Case True
        Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
          With Target.Offset(0, 2)
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End With
          If InStr(1, Target.Value, "-") > 0 Then
            Target.Offset(0, 15).Value = Trim(Split(Target.Value, "-")(1))
          End If
         
        Case Target.Value = "Assigned"
          Target.Offset(0, 10).Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
         
        Case Target.Value = "Reset Hold Date"
          Target.Offset(0, 2).Value = ""
         
        Case Target.Value = "Reset Assigned Date"
          Target.Offset(0, 10).Value = ""
         
      End Select
  End If
End Sub

But if you are going to modify several cells at the same time, then use the following:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Range("Z:Z"))
  If Not rng Is Nothing Then
    For Each c In rng
      Select Case True
        Case InStr(1, c.Value, "On Hold", vbTextCompare) > 0
          With c.Offset(0, 2)
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End With
          If InStr(1, c.Value, "-") > 0 Then
            c.Offset(0, 15).Value = Trim(Split(c.Value, "-")(1))
          End If
         
        Case c.Value = "Assigned"
          c.Offset(0, 10).Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
         
        Case c.Value = "Reset Hold Date"
          c.Offset(0, 2).Value = ""
         
        Case c.Value = "Reset Assigned Date"
          c.Offset(0, 10).Value = ""
         
      End Select
    Next
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

This worked, thank you! Only one cell is being modified at a time, so I used the first set of code.

A couple of observations:

If the status is changed and reverted back to “On Hold” or “Assigned” it updates the time stamp. I want that original timestamp to remain intact, with the only exception being if the status is changed to “Reset Hold Date” or “Reset Assigned Date”. In that case, the timestamp should disappear and would allow the user to update the timestamp by selecting the On Hold or Assigned status again. How can we modify the code so it responds in this manner?

Also, and I should have mentioned this previously, there are two different types of On Hold statuses; For example; “ On Hold - Chris” and “On Hold - Chris (internal hold)”. I would like it to return only the name “Chris” in both scenarios. The delimiter is set to “-“, so I wonder what would be the best approach to prevent the “(internal hold)” from appearing?

Lastly, I’d like to add the ability for the “Assigned” status to input a name in an adjacent column as well (Target.Offset would be (0, 16). I will be adding names to the status, for example; “Assigned - Nicole”. I tried mimicking the code that adds the name from the “On Hold” status, just modifying the target value to “Assigned”, and placing it below the On Hold code, but it gave me an error.

I really appreciate your help with this!

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Select Case True
      Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End If
        End With
        If InStr(1, Target.Value, "-") > 0 Then
          Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
        End If
        
      Case Left(Target.Value, 8) = "Assigned"
        With Target.Offset(0, 10)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            If InStr(1, Target.Value, "-") > 0 Then
              Target.Offset(0, 16).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
            End If
          End If
        End With
        
      Case Target.Value = "Reset Hold Date"
        Target.Offset(0, 2).Value = ""
        
      Case Target.Value = "Reset Assigned Date"
        Target.Offset(0, 10).Value = ""
        
    End Select
  End If
End Sub

 

[deftincu]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Select Case True
      Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End If
        End With
        If InStr(1, Target.Value, "-") > 0 Then
          Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
        End If
       
      Case Left(Target.Value, 8) = "Assigned"
        With Target.Offset(0, 10)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            If InStr(1, Target.Value, "-") > 0 Then
              Target.Offset(0, 16).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
            End If
          End If
        End With
       
      Case Target.Value = "Reset Hold Date"
        Target.Offset(0, 2).Value = ""
       
      Case Target.Value = "Reset Assigned Date"
        Target.Offset(0, 10).Value = ""
       
    End Select
  End If
End Sub

This worked perfectly! Thank you so much!

 

[deftincu]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Select Case True
      Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
          End If
        End With
        If InStr(1, Target.Value, "-") > 0 Then
          Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
        End If
       
      Case Left(Target.Value, 8) = "Assigned"
        With Target.Offset(0, 10)
          If .Value = "" Then
            .Value = Format(Date + Time, "mm/dd/yyyy hh:mm")
            If InStr(1, Target.Value, "-") > 0 Then
              Target.Offset(0, 16).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
            End If
          End If
        End With
       
      Case Target.Value = "Reset Hold Date"
        Target.Offset(0, 2).Value = ""
       
      Case Target.Value = "Reset Assigned Date"
        Target.Offset(0, 10).Value = ""
       
    End Select
  End If
End Sub

@DanteAmor - hope all is well! I was hoping you can assist me again, pertaining to the same code mentioned above.

In the code, the “On Hold” status is driving both the time stamp and the person’s name that is entered into column 15. I now need the On Hold status to initiate only the time stamp, and the name needs to be driven by a new status not previously mentioned. Similar to the On Hold status, it will have different names associated with it - The new status is “New - Chris to Review”, “New - Joe to Review”, etc. So when either of these statuses are chosen, it should populate Chris, Joe, etc in column 15.

I would like to retain the same functionality of everything else in the code, just essentially change the piece of the code that is highlighted in the screenshot below.

Your assistance will be greatly appreciated!