VBA to Set a Cell Comment, Delete Comment and Add Comments in multiple Cells based on Condition

Рет қаралды 5,137

3 жыл бұрын

This Excel Comments Macro Tutorial explains, how to add or remove cell comments. Also, how to insert comments in multiple cells or range when condition is met. We can control comments in excel easily using VBA Code.

Пікірлер: 20

@silverlicious2086 3 жыл бұрын

Very good example. I can use this to analyze 1 month of data after collecting it. Thank you.

@ExcelDestination 3 жыл бұрын

Glad it was helpful!

@rlcornish5400 2 жыл бұрын

Fantastic tutorial. Very straightforward and easily digested. Thank you.

@ExcelDestination 2 жыл бұрын

You're very welcome!

@PatrickAngwin 2 жыл бұрын

Great tutorial, thank you. I have a similar question to a couple of the other commenters above. What if I had a range of cells with calculated numbers in them, say A5:B10, and I wanted to append comments to each of those cells with the text for those comments in range D5:E10. How would I go about this?

@compukeychannel9592 2 жыл бұрын

best explanation sir

@ExcelDestination 2 жыл бұрын

Thanks and welcome

@vivekmusafir4492 2 жыл бұрын

And can you please help on how to give range of some other cell to put value in comment

@ramchandralokhande4347 Жыл бұрын

Comments to add or remove based on cell color

@firasath 3 жыл бұрын

Hi, very useful info. Can we insert comments, based on a formula result, to a range of cells. The formula result could be different. Suppose I want to check the values in A3:T3 and insert a comment by looking up values from different cells using vlookup or hlookup. Please suggest.

@ExcelDestination 3 жыл бұрын

Yes, absolutely

@firasath 3 жыл бұрын

@@ExcelDestination Thanks for yout prompt response. Can you please help me doing so, I can share with you the template sheet. Please advise.

@timkropf4648 3 жыл бұрын

I have a very similar issue to this. I'm trying to create threaded comments, then insert content (text) from a different cell range (on another sheet) into the threaded comments. My experience level in VBA is low so I'm searching many videos and try to find the missing pieces to accomplish this. Any help would be appreciated! Also, when I use your formula above to extract comments, I don't know how to handle the error if I press the button to add comments and comments already exist.

@nadermounir8228 3 жыл бұрын

Thank you for this video. Very nice Wouldn't it be better to use this code in a Calculate event ? Since these values can change anytime and so the comments would change or be removed accordingly

@silverlicious2086 3 жыл бұрын

Good point. He is using an "If" statement. How and where would you put this to make it dynamic as you said? Some sort of conditional formatting rule?

@nadermounir8228 3 жыл бұрын

@@silverlicious2086 if these numbers change based on a formula, then it would be best if Excel can automatically remove or add the comment according to the cell value without u having to click on a button. It would be like a formula This can be done using a Calculate event. For some unknown reason not many VBA KZfaqr show these events on their tutorials while I think they are the most useful tools in VBA

@silverlicious2086 3 жыл бұрын

@@nadermounir8228 I've not used that event precisely and not played with this concept before. I guess it's like when the worksheet is re-calculated such as when opened. Or somehow when Excel automatically recognizes the target has been changed as in a formula as you mentioned. Thank you for your input and I will try to learn more about the Calculate event itself. And yes, I've not noticed this calculate event shown on KZfaq..but then again I've not searched for it. Thanks for the idea.

@ExcelDestination 3 жыл бұрын

Yeah

@vivekmusafir4492 2 жыл бұрын

Thank you so much…I have a situation where value is in millions and I have converted value in Mn. By dividing by million and joining text as “Mn.” ( 2,00,000 will reflect as 2 Mn.) but I want absolute value i.e. 2,00,000 reflecting in comment…can you please help in this situation

@ZanahoriaCara 2 жыл бұрын

Check out videos on "custom formatting". It will let you keep the cell value as 2,000,000 (for convenient use in later formulas) but display it with whatever truncation or formatting you want, ie "2.00M" or "2 Million" or "2.0 milleroos", etc.