Move up the queue when modified

[electronictokwa]

Hi guys! I hope you can help me out

I have a table below with test names and modifiers (yes or no). What I need is for the bottom name to go up the queue every time it is modified by entering a value in cell next to it. It is even possible? Any ideas?

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>


[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Name[/TH]
[TH]Modified (Y/N)[/TH]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[electronictokwa]

I missed a few information. The bottom name will go up the queue when the cell next to it is modified, and the remaining names will move down the queue; all the listed names will go into rotation. Am I making any sense hehe. Thanks in advance!

 

[Marcelo Branco]

Assuming your table in A1:B6, maybe...

Right-click in the sheet tab, pick View Code
Paste the code below in the right panel

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vRng As Variant
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Application.EnableEvents = False
        vRng = Range("A2:B5").Value
        Range("A2:B2").Value = Range("A6:B6").Value
        Range("A3:B6").Value = vRng
        Application.EnableEvents = True
    End If
    
End Sub

M.

 

[electronictokwa]

Assuming your table in A1:B6, maybe...

Right-click in the sheet tab, pick View Code
Paste the code below in the right panel

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vRng As Variant
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Application.EnableEvents = False
        vRng = Range("A2:B5").Value
        Range("A2:B2").Value = Range("A6:B6").Value
        Range("A3:B6").Value = vRng
        Application.EnableEvents = True
    End If
    
End Sub

M.

Wow! It worked! Thank you so much! I'll definitely come back if I have more questions

 

[Marcelo Branco]

Wow! It worked! Thank you so much! I'll definitely come back if I have more questions

You are welcome and thanks for the feedback.

M.

 

[electronictokwa]

Got a few more questions

Is it possible for multiple user to access and edit this file? Lastly, is it possible for the table to grab the user's NT username or ID to be logged in the cell to the right of the field every time he/she modifies the bottom name? Thanks again!

 

[Marcelo Branco]

Hi,

1. Take a look at
Use a shared workbook to collaborate - Excel - Office.com

2. Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vRng As Variant
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Application.EnableEvents = False
        vRng = Range("A2:C5").Value
        Range("A2:C2").Value = Range("A6:C6").Value
        Range("A3:C6").Value = vRng
        Range("C6").Value = Environ("Username") & " at " & Now
        Application.EnableEvents = True
    End If
    
End Sub

M.

 

[Marcelo Branco]

Correction

2.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vRng As Variant
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Application.EnableEvents = False
        vRng = Range("A2:C5").Value
        Range("C6").Value = Environ("Username") & " at " & Now
        Range("A2:C2").Value = Range("A6:C6").Value
        Range("A3:C6").Value = vRng
        Application.EnableEvents = True
    End If
    
End Sub

M.

 

[electronictokwa]

Awesome! Worked like a charm! Thanks so much Marcelo! I'll post a reply again if I have more questions. Thank you again!

 

[Marcelo Branco]

Awesome! Worked like a charm! Thanks so much Marcelo! I'll post a reply again if I have more questions. Thank you again!

You are welcome!

M.
ps: for further questions you can open a new thread, if you prefer.