Comment section of next step list, capture date, feedback at the current date and change the revision of the next step

[slayer1957]

Good day,

I have a next step list where each next step listed has a comment section in column G, the comment section is usually only one cell with the text. Every meeting the feedback must be captured with today's date then feedback from the project manager and then if next step is not completed it must up the revision of the next step which is in column E and comment must be added

D	E	F	G
Estimated completion date	Revision	Completed Y/N	Comments/Feedback
31/12/2019	rev04	No	10/08/2018-Arranged a meeting with instruments for possible solutions. Currently no as-builts. 12/11/2018- Discuss with instruments to tie in to the PLC, get drawings updated and have session to discuss possible tie-ins. 13/02/2019- Red line drawings to be updated 13/05/2019- Follow up if next step is still needed <tbody> </tbody>
25/12/2019	rev05	No	30/8/2018- APT study completed. Awaiting final recommendation from Electrical engineering. 10/08/2018-Waiting for inventory to make final call. 12/11/2018 - Was presented and approved at inventory governance for a spare. 13/02/2019- Presented and 1 was allocated 13/05/2019- Confirmed 2 to be spare, Move one at substation to store, follow up meeting on 17 May 2019 <tbody> </tbody>

<tbody>
</tbody>

I need to try and do this with a popup message and input the data and let it save as the example, for instance i click on cell of the comment/feedback on the specific meeting day (today), it must popup a text box and feedback must be written and it must save it as continuation of the current cell text.

Can this be done?

 

[My Aswer Is This]

This script should do what you want:
I prefer double click instead of Click to get a script to run.
Clicking can be done accidentally as double clicking probable cannot happen by accident

So when you double click on any cell in column "G"
You will get a Input box popup where you can enter your Text and then the script will do what you want.

The only exception is the script will put a revision number in column "E" but not the Text Rev and then the number. I was not able to do that. It's sort of complicated for me to get the number in the cell when it also has Text and then add one to the number so I hope this will work. The script will not run if you have "Yes" in column "F" you will get a warning saying that project is completed.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/17/2019  12:19:51 PM  EDT
If Target.Column = 7 Then
Cancel = True
Dim ans As String
Dim Previous As String
Dim r As Long
If Target.Offset(, -1).Value = "Yes" Then MsgBox "This project is completed": Exit Sub
r = Target.Offset(, -2).Value + 1
Previous = Target.Value
ans = InputBox("Enter your data below", "This will be revision  " & r)
Target.Offset(, -2).Value = Target.Offset(, -2).Value + 1
If Previous <> "" Then
Target.Value = Previous & vbNewLine & Date & "-" & ans
Else
Target.Value = Date & "-" & ans
End If
End If
End Sub

 

[My Aswer Is This]

I figured out how to put Rev and a number in column E

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/17/2019  11:43:53 PM  EDT
If Target.Column = 7 Then
Cancel = True
On Error GoTo M
Dim ans As String
Dim Previous As String
Dim r As Long
    If Target.Offset(, -1).Value = "Yes" Then MsgBox "This project is completed": Exit Sub
    
    If Target.Offset(, -2).Value = "" Then
        r = 1
    Else
        r = Right(Target.Offset(, -2).Value, Len(Target.Offset(, -2).Value) - 4) + 1
    End If
Previous = Target.Value
ans = InputBox("Enter your data below", "This will be revision " & r)
    If Target.Offset(, -2).Value = "" Then
        Target.Offset(, -2).Value = "Rev 1"
    Else
        Target.Offset(, -2).Value = "Rev " & Right(Target.Offset(, -2).Value, Len(Target.Offset(, -2).Value) - 4) + 1
    End If
    If Previous <> "" Then
        Target.Value = Previous & vbNewLine & Date & "-" & ans
    Else
        Target.Value = Date & "-" & ans
    End If
End If
Exit Sub
M:
MsgBox "We had a problem." & vbNewLine & "Maybe you have some odd value in Column E"
End Sub

 

[My Aswer Is This]

I provide you a answer this question but never heard back from you if this worked for you.

 

[slayer1957]

I have tested it and it works beautifully, thank you so much for your assistance.

There is one minor problem when the text box appears and you press cancel it still puts date in the cell as continued part of the text.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/29/2019  2:35:13 PM  EDT
If Target.Column = 7 Then
Cancel = True
On Error GoTo M
Dim ans As String
Dim Previous As String
Dim r As Long
    If Target.Offset(, -1).Value = "Yes" Then MsgBox "This project is completed": Exit Sub
    
    If Target.Offset(, -2).Value = "" Then
        r = 1
    Else
        r = Right(Target.Offset(, -2).Value, Len(Target.Offset(, -2).Value) - 4) + 1
    End If
Previous = Target.Value
ans = InputBox("Enter your data below", "This will be revision " & r)
If Len(ans) = 0 Then MsgBox "You clicked Cancel": Exit Sub
    If Target.Offset(, -2).Value = "" Then
        Target.Offset(, -2).Value = "Rev 1"
    Else
        Target.Offset(, -2).Value = "Rev " & Right(Target.Offset(, -2).Value, Len(Target.Offset(, -2).Value) - 4) + 1
    End If
    If Previous <> "" Then
        Target.Value = Previous & vbNewLine & Date & "-" & ans
    Else
        Target.Value = Date & "-" & ans
    End If
End If
Exit Sub
M:
MsgBox "We had a problem." & vbNewLine & "Maybe you have some odd value in Column E"
End Sub

 

[slayer1957]

Works as requested, Thanks again, just on the "Date", how to change format to be 03/12/2019, currently it 03-December-2019