Add extra line of code to VBA to copy cell value across sheet

[MeaclH]

Hi all,

I am trying to evolve this VBA code to it's final state. All of my code basically comes from you fine folk as I am not very savvy.
The below code works perfectly, although I want to add one bit of functionality.
The code works by identifiying if a cell is changed from a value of "EDO" or "EDO-U" to any of the following values "0", "MTP", "ADMIN", "TRAINING", "ADHOC".
If the cell is changed it changes the formatting and triggers a MsgBox where a note is added.

The new bit of code I would like some help with is, that if a cell is changed from "EDO" or "EDO-U" to any of the following "0", "MTP", "ADMIN", "TRAINING", "ADHOC", that the original cell value is input in a column further across the sheet. For example G28 is changed from "EDO" to "MTP", the original value "EDO" is dumped into cell AT28 before the rest of the code is triggered.

Thanks so much for any input you may have. I have pasted the entire sheet code below, but this is specifically the EDO/EDO-U section.

cheers,
Hayden

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Resp As String
Dim prevValue
prevValue = pVal

On Error GoTo ExitNow
Application.EnableEvents = False
If Target.CountLarge = 1 And Not Intersect(Target, Range("G11:T178")) Is Nothing Then '<--- Change Target Range Here
    If swapval = False Then
'---EDO/EDO-U
        If prevValue = "EDO" Or prevValue = "EDO-U" Then
             Select Case Target.Value
                  Case "0", "MTP", "ADMIN", "TRAINING", "ADHOC"
                    With Target
                    ActiveSheet.Unprotect
                    .Interior.Color = RGB(120, 210, 91)
                    .Font.Color = RGB(255, 0, 0)
                    'Input Box below. change text and title as needed:
                    Resp = Application.InputBox("You are allocating an open shift to a DAO on an EDO.         Please include details of when this shift was added and notify the DAO at the earliest opportunity.", _
                    Title:="Open shift added on an EDO")
                    ActiveSheet.Protect DrawingObjects:=False
                End With
            End Select
        Else
'-------OFF/OFF-U
            If prevValue = "OFF" Or prevValue = "OFF-U" Then
             Select Case Target.Value
                  Case "0", "MTP", "ADMIN", "TRAINING", "ADHOC"
                    With Target
                    ActiveSheet.Unprotect
                    .Interior.Color = RGB(255, 255, 1)
                    .Font.Color = RGB(255, 0, 0)
                    'Input Box below. change text and title as needed:
                    Resp = Application.InputBox("You are allocating an open shift to a DAO on a day OFF.         Please include details of when this shift was added and notify the DAO at the earliest opportunity.", _
                    Title:="Open shift added on a day OFF")
                        ActiveSheet.Protect DrawingObjects:=False
                    End With
                End Select
            End If
        End If
        swapval = False
    End If
'---Absenteeism Details
    With Target
        Select Case .Value
            Case "SDO", "STFN", "CDO", "CTFN", "SPDO" '<- Add more trigger values here if required
                Resp = Application.InputBox("Please insert details of absenteeism", _
                Title:="Absenteeism Details")
            Case "OFF-U", "EDO-U" '<- Add more trigger values here if required
                Resp = Application.InputBox("Please insert details of DAO request to be marked unavailable on OFF/EDO.", _
                Title:="OFF/EDO Unavailability Details")
        End Select
    End With
'---DAO Shift Extension Confirmation/Decline
    With Target
        If InStr(1, .Value, "OK") > 0 Then
            Resp = Application.InputBox("Please enter details of when this shift extension was confirmed by the DAO. " & _
            "Including time and date and how it was confirmed", "DAO Shift Extension Confirmation", , , , , , 2)
        Else
            If InStr(1, .Value, "DEC") > 0 Then
                Resp = Application.InputBox("Please enter details of when this shift extension was declined by the DAO. " & _
                "Including time and date when it was declined", "DAO Shift Extension Declined", , , , , , 2)
        Else
            If InStr(1, .Value, "dec") > 0 Then
                Resp = Application.InputBox("Please enter details of when this shift extension was declined by the DAO. " & _
                "Including time and date when it was declined", "DAO Shift Extension Declined", , , , , , 2)
        Else
            If InStr(1, .Value, "?") > 0 Then
                Resp = Application.InputBox("Please enter details of when this shift extension was added to the DAOs roster. " & _
                "Including time and date when it was added", "DAO Shift Extension added", , , , , , 2)
        Else
            If InStr(1, .Value, "ok") > 0 Then
            Resp = Application.InputBox("Please enter details of when this shift extension was confirmed by the DAO. " & _
            "Including time and date and how it was confirmed", "DAO Shift Extension Confirmation", , , , , , 2)
                        End If
                    End If
                End If
            End If
        End If
    End With
    Call AddComment(Target, Resp)
End If

ExitNow:
Application.EnableEvents = True
End Sub

 

[myall_blues]

Hi Hayden

Try this. Note you need to move the call to unprotect/protect the sheet:

'---EDO/EDO-U
    If prevValue = "EDO" Or prevValue = "EDO-U" Then
        Select Case Target.Value
            Case "0", "MTP", "ADMIN", "TRAINING", "ADHOC"
                ActiveSheet.Unprotect
                Range("AT" & Target.Row).Value = prevValue
                With Target
                    .Interior.Color = RGB(120, 210, 91)
                    .Font.Color = RGB(255, 0, 0)
                    'Input Box below. change text and title as needed:
                    Resp = Application.InputBox("You are allocating an open shift to a DAO on an EDO.         Please include details of when this shift was added and notify the DAO at the earliest opportunity.", _
                    Title:="Open shift added on an EDO")
                End With
                ActiveSheet.Protect DrawingObjects:=False
        End Select
    Else
'-------OFF/OFF-U