Using VBA to add a name to a cell

D

deftincu

New Member
Joined
Jul 19, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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?
image002.png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
If you modify only one cell at a time, then a For each R is not necessary, so it could be:

VBA Code: 
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:

VBA Code: 
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
----- --
 
Upvote 0
DanteAmor said:
If you modify only one cell at a time, then a For each R is not necessary, so it could be:

VBA Code: 
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:

VBA Code: 
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
----- --
Click to expand...
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!
 
Upvote 0
Try:

VBA Code: 
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
 
Upvote 0
Solution
DanteAmor said:
Try:

VBA Code: 
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
Click to expand...
This worked perfectly! Thank you so much!
 
Upvote 1
You must log in or register to reply here.

Similar threads

D
  • Question Question
Static Timestamp
Replies
4
Views
942
Fluff
Fluff
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
173
StephenCrump
S
R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
158
kvsrinivasamurthy
K
Z
  • Solved
VBA: ClearNotes in another Table
Replies
1
Views
139
zero269
Z
K
  • Question Question
Stocktake of Barcoded Inventory in Excel using a Barcode Scanner
Replies
2
Views
120
Kobus Horn
K

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top