Double Click on linked cell to edit the comments & save in the same cell.

[ashyn]

Hello Everyone,
I work as part of a hotel & am trying to work on a report.

Please can someone help me find an answer.
I have a table with Column C where employees assigned this task put in comments on a daily basis.

I review this report & make changes if any before the report is shared to mt Boss.
The comments are very important for decisions to be made.

Is there a Macro that can help me on this ? Can i do the changes to the comment when I double click ?

SHEET 1	Coulmn A	Column B	Column C	ColumnD
Row 1	Unit	Trend	Comments
Row 2	Reception	Upwards	10 Regular Customers acknowledged improvement in last 9 months	Col C is populated from another Sheet. SHEET2. When I double click Cell C2, it should allow me to edit the comment & save the changes in Cell C2 only.
Row 3	Laundry	No Change	No Comments
Row 4	Security	Downwards	2 cases booked on 08Apr, 2018
Row 5	etc..

<tbody>
</tbody>

Please Guide me find an answer,
Thanks in Advance.
Steeve.

 

[Luigi802]

.

 

[My Aswer Is This]

Try this:
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

Double click any cell in column C

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 5/5/18 3:10 PM EDT
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cancel = True
ans = InputBox("Modify Me", "Previous Data:  " & Target.Value, Target.Value)
Target.Value = ans
End If
End Sub

 

[ashyn]

Column C (as per below Table) is linked to another sheet.

Column C is formulated (=RawData Apr2018!H2) which is linked from another sheet named "RawData Apr2018".

Everytime, I need to convert this cell (Copy > paste special values) then, edit the comments.
I am looking for a macro that would allow me to double click on cell C2 on Sheet 1 to edit the comment & save it on the same cell.


Hello Everyone,
I work as part of a hotel & am trying to work on a report.

Please can someone help me find an answer.
I have a table with Column C where employees assigned this task put in comments on a daily basis.

I review this report & make changes if any before the report is shared to mt Boss.
The comments are very important for decisions to be made.

Is there a Macro that can help me on this ? Can i do the changes to the comment when I double click ?

SHEET 1	Coulmn A	Column B	Column C	ColumnD
Row 1	Unit	Trend	Comments
Row 2	Reception	Upwards	10 Regular Customers acknowledged improvement in last 9 months	Col C is populated from another Sheet. SHEET2. When I double click Cell C2, it should allow me to edit the comment & save the changes in Cell C2 only.
Row 3	Laundry	No Change	No Comments
Row 4	Security	Downwards	2 cases booked on 08Apr, 2018
Row 5	etc..

<tbody>
</tbody>

Please Guide me find an answer,
Thanks in Advance.
Steeve.

 

[ashyn]

Thank you very much. It works perfect, yet may I ask you few more questions on this Macro.

1. When i double click any cell in Column C it does provide the pop up but when i click cancel, the existing data on that cell gets deleted. Please advise.

2. The Box is too small & the complete comment cannot be read to Edit. Can the Box be made bigger for large comments to be read ? Please Help.

Steeve

 

[My Aswer Is This]

Why the need to post your question twice?
When you say comment. I assume you mean cell value.
Or do you mean a comment attached to the cell.
Excel has a add comment to cell function. But I'm not sure that can be the result of a formula.

 

[ashyn]

Apologies, I was still typing the message & it went through in spilts.

When I say Comment, its Cell Value.
Column C is updated with Comments. Yet its not directly updated in that cell. Column C is linked from another sheet.

Your code works perfect, except for a few small questions. I have mentioned in earlier thread.

 

[My Aswer Is This]

I have no other suggestion

Like how much data is in your comment.
I mean are we taking about 25 characters or 150 or more characters.
And if user double clicks cell and then clicks cancel that maybe could be fixed but what other if or possibilities might you have?

 

[My Aswer Is This]

How about we just change the cells value to value and now data is still there but is not dependent on formula.
Then you click on cell and make changes.

If that works try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cancel = True
Target.Value = Target.Value
End If
End Sub

 

[ashyn]

Generally its huge characters ie: 150 or more.
We do a lot of comments revision. No options from my end, but to manually continue to copy complete data into another file, make necessary changes & copy them back to this file.

Main problem we encounter is the comment gets mixed up. Comment A gets updated against Header B etc..