Elaborating A VB IF senario

[rouzacct]

Hello

i currently have this code to help me see what date/time/user a cell in the ("R:R") had its value changed.
----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("R:R")) Is Nothing Then
Target.Offset(0, 1) = Now
Target.Offset(0, 2) = Environ("UserName")

End If
End Sub
-------------------------------------

I would like to elaborate it a little

it would be great to have date and time separated
so that when a cell in ("R:R") is changed
the 3 cells on its right "S-T-U" auto populate date/time/user

there are 4 words that could be written in that column

if "RET" i would like it to auto populate date/time/user in the next 3 columns "S-T-U" and also in 3 other columns on the right (any 3 columns next to each other i will check the offsets after )

if "PROBLEM" i would like it to auto populate date/time/user in the next 3 columns "S-T-U" and also in 3 other columns on the right (any 3 columns next to each other i will check the offsets after)

IF "DEAD" i would like it to auto populate date/time/user in the next 3 columns "S-T-U" and also in 3 other columns on the right (any 3 columns next to each other i will check the offsets after)

IF "GOOD" i would like it to auto populate date/time/user in the next 3 columns and also in 3 other columns on the right (any 3 columns next to each other i will check the offsets after)

so that

It always shows the last date/time/user that a cell in R:R was changed in "S-T-U" but also keeps a record of the last status by doubling up the date/time/user into separate columns by RET - PROBLEM - DEAD - GOOD
and not overriding the date/time/user of the last change from any of the words.

ANY HELP WOULD BE GREATLY APPRECIATED

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OSet As Integer
    
    If Not Intersect(Target, Range("R:R")) Is Nothing Then
    
        If Target.Count = 1 Then
            
            [COLOR="Green"]' Log Date\Time\Name to columns S-T-U[/COLOR]
            Target.Offset(0, 1) = Date
            Target.Offset(0, 2) = Time
            Target.Offset(0, 3) = Environ("UserName")
            
            Select Case UCase(Target)
                Case "RET": OSet = 4
                Case "PROBLEM": OSet = 7
                Case "DEAD": OSet = 10
                Case "GOOD": OSet = 13
            End Select
            
            [COLOR="Green"]' Log Date\Time\Name to specific columns based on target[/COLOR]
            Target.Offset(0, OSet) = Date
            Target.Offset(0, OSet + 1) = Time
            Target.Offset(0, OSet + 2) = Environ("UserName")
            
    End If: End If
End Sub

 

[rouzacct]

thank you this is great I cant believe such a quick response..
i wish i had something i could help you with lol


It seems to not stop running and wont get out of loop and crashed excel
when the value is deleted

would you please look into it again for a sec please

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OSet As Integer
    
    If Not Intersect(Target, Range("R:R")) Is Nothing Then
    
        If Target.Count = 1 Then
        
            Application.EnableEvents = False    'suspend events
        
            If Target = "" Then
                'Clear previous Date\Time\Name?
                Target.Offset(, 1).Resize(, 15).ClearContents
            Else
            
                ' Log Date\Time\Name to columns S-T-U
                Target.Offset(0, 1) = Date
                Target.Offset(0, 2) = Time
                Target.Offset(0, 3) = Environ("UserName")
                
                Select Case UCase(Target)
                    Case "RET": OSet = 4
                    Case "PROBLEM": OSet = 7
                    Case "DEAD": OSet = 10
                    Case "GOOD": OSet = 13
                End Select
                
                ' Log Date\Time\Name to specific columns based on target
                Target.Offset(0, OSet) = Date
                Target.Offset(0, OSet + 1) = Time
                Target.Offset(0, OSet + 2) = Environ("UserName")
            
            End If:
            
            Application.EnableEvents = True    'resume events
            
        End If: End If
    
End Sub

 

[rouzacct]

SO SORRY FOR THE DOUBLE REPLY BACK I TRIED TO EDIT MY POST BUT PASSED THE 10 MIN LIMIT TO EDIT SO I HAD TO WRITE IT AGAIN


thank you this is it .I cant believe such a quick response..
i wish i had something i could help you with lol

2 things need a little tune up .
the column R is currently getting overridden by date and the user name is repeated twice in T and U and should only be in U
SO R=word S= Date T= Time U= User

it should keep the value as is (good ret problem dead ) and populate the 3 info next to it.

and
It seems to not stop running and wont get out of loop and crashed excel
when the value is deleted

would you please look into it again for a sec please
please allow any other words to be written in a cell in R:R in a case that its not one of the known words let it just populate the 3 infos in S-T-U and not have 3 dedicated columns for other words ..

see if you can have it keep the date and info when the words are changed my goal is to have a time line and records of the words
so if i change from ret to dead or any other combination ,,
make it keep its last (3 infos) in the respective collumns of that word

I just changed Target.Offset(0, OSet ) = Date to Target.Offset(0, OSet + 1) = Date
and the override effect is fixed .,

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OSet As Integer
    
    If Not Intersect(Target, Range("R:R")) Is Nothing Then
    
        If Target.Count = 1 Then
        
            Application.EnableEvents = False    'suspend events
        
            If Target = "" Then
                'Clear previous Date\Time\Name?
                Target.Offset(, 1).Resize(, 15).ClearContents
            Else
            
                ' Log Date\Time\Name to columns S-T-U
                Target.Offset(0, 1) = Date
                Target.Offset(0, 2) = Time
                Target.Offset(0, 3) = Environ("UserName")
                
                Select Case UCase(Target)
                    Case "RET": OSet = 4
                    Case "PROBLEM": OSet = 7
                    Case "DEAD": OSet = 10
                    Case "GOOD": OSet = 13
                End Select
                
                [COLOR="Red"]If OSet > 0 Then[/COLOR]
                    ' Log Date\Time\Name to specific columns based on target
                    Target.Offset(0, OSet) = Date
                    Target.Offset(0, OSet + 1) = Time
                    Target.Offset(0, OSet + 2) = Environ("UserName")
                [COLOR="Red"]End If[/COLOR]
            
            End If
            
            Application.EnableEvents = True    'resume events
            
        End If: End If
    
End Sub

 

[rouzacct]

and i just tested it again and it all seems fine !!!
THANK YOU SO MUCH unless you see anything should be fixed in the code based on what i just wrote 2 minutes a go .. i would say its now good

THANKS AGAIN

 

[rouzacct]

OK FIRST I AM SO SORRY FOR ALL THIS CONFUSION
you are so fast i was panicking trying to get a reply back to you

SO FAR EVERYTHING IS AS WANTED

just one detail and it should be running like a champ


would u please have the code keep all the info on that row and not delete anything when R is cleared content ( like when i click on deleted )

i will delete the date/time/ user myself when needed

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OSet As Integer
    
    If Not Intersect(Target, Range("R:R")) Is Nothing Then
    
        If Target.Count = 1 And Target(1) <> "" Then
        
            Application.EnableEvents = False    'suspend events
        
            ' Log Date\Time\Name to columns S-T-U
            Target.Offset(0, 1) = Date
            Target.Offset(0, 2) = Time
            Target.Offset(0, 3) = Environ("UserName")
            
            Select Case UCase(Target)
                Case "RET": OSet = 4
                Case "PROBLEM": OSet = 7
                Case "DEAD": OSet = 10
                Case "GOOD": OSet = 13
            End Select
            
            If OSet > 0 Then
                ' Log Date\Time\Name to specific columns based on target
                Target.Offset(0, OSet) = Date
                Target.Offset(0, OSet + 1) = Time
                Target.Offset(0, OSet + 2) = Environ("UserName")
            End If
            
            Application.EnableEvents = True    'resume events
                    
        End If: End If
    
End Sub

 

[rouzacct]

IM Sure you already know but i want to state the obvious

You just helped me out so much you cut down a week worth time i had to get this fixed
so i can move on to the next steps and

u just saved me so much stress and data loss and helped me do what i just couldn't

THANK YOU SO MUCH


IF ONLY ALL THE PEOPLE ON EARTH HAD YOUR TYPE PATIENCE
the world would be a better place !!


THANKS A MILL !!