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

slayer1957

Board Regular
Joined
Jan 9, 2017
Messages
50
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Estimated completion date[/TD]
[TD]Revision[/TD]
[TD]Completed Y/N[/TD]
[TD]Comments/Feedback[/TD]
[/TR]
[TR]
[TD]31/12/2019[/TD]
[TD]rev04[/TD]
[TD]No[/TD]
[TD][TABLE="width: 312"]
<tbody>[TR="class: grid"]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]25/12/2019[/TD]
[TD]rev05[/TD]
[TD]No[/TD]
[TD][TABLE="width: 312"]
<tbody>[TR="class: grid"]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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

Code:
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
 
Upvote 0
I figured out how to put Rev and a number in column E

Try this:
Code:
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
 
Upvote 0
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.
 
Upvote 0
Try this:
Code:
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
 
Upvote 0
Works as requested, Thanks again, just on the "Date", how to change format to be 03/12/2019, currently it 03-December-2019
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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