39 thoughts on “Comment Control”

1. Hi Juan Pablo!

   Your add-in looks great but I’m getting a compile error in hidden module when I go to format the background.
   Due to a missing reference:
   Microsoft Common Dialog Control (SP3)

   Could you please tell me where to install this?

   Cheers,
   Rob

2. Hi Rob,

   I put another version using late binding in there, but I do use the Common Dialogs file (ComDlg32.ocx).

   You can download it here for example:

   http://tinyurl.com/3m8u3

3. Hi Juan :)

   Nice one.
   How about the ability to change the comments shape.

   As for Microsoft Common Dialog Control (SP3) that’s one of the reasons I don’t use this in my addins as not everyone has it OR if they may have problems registering it.

4. Thanks Ivan :),

   I thought about including that option, as well as setting a picture to be the background, but then I thought that that might complicate things too much, and decided to leave it like that.

   So, the Common dialog is not that common then ?

5. I didn’t know you could change the commentbox shape.

   ActiveCell.Comment.Shape.AutoShapeType = msoShape16pointStar

   Amazing!

6. Something I have been thinking about for some time (this subject jogged my memory): is there a way to change the font in message boxes/input boxes?

7. Very cool add in, Juan!

   I¥ve also been able to learn a lot from the way you programmed this add-in!

8. Nice utility Juan.

   One quick change when using custom text would be to use the vbLF constant instead of vbNewLine in the routine CC_InsertComment.
   That way you will not get the little square after the text.

9. Hi, I have the ComDlg32.ocx file, and even tried unregistering and re-registering it, but still get an error at this line each time:

   Set Dlg = CreateObject(“MSComDlg.CommonDialog”) ‘New MSComDlg.CommonDialog

   I don’t see a reference to the “Microsoft Common Dialog Control” in my VBA project references, nor does it come up in the list. Any ideas?

   Thanks!
   Matt H

10. Rob: Yep, you can do lots of stuff with comments. It can get quite messy ;-)

    Alan: Thanks ! glad you liked it !

    Andy: I wasn’t getting the box on my PC, but just changed it anyway, and put a new version too that does not require the CommonDialog file.

    Matt H: What error are you getting ? But could you try to download it again ? I just put a version that doesn’t require the CommonDialog file to exist. It will use the builtin dialogs if the comdld32.ocx call fails.

11. The new version works great. Thanks for the nice free utility, Juan! :) -Matt H

12. Can anyone help me export an excel .xls to a word .doc?

    I am trying to get this form that I spent hours programming to find that when I cut and paste to Word, the cell formatting become skewed.

    I have tried to start with a table in Word and then copy the .xls into it, but that did not work very well.

    Any help is appreciated.

    Chris

13. Hi Master Juan Pablo,

    OK, I’m the first one to admit that I don’t like all the whistle & bells people want to add to their application.

    After all, Excel is per se a spreadsheet-software.

    Anyway, I downloaded and installed it. It works with my swedish version! I’m still surprised when add-ins work as expected with my local version.

    It’s a nice and a nifty little tool You’ve developed and will be highly appreciated for users who want to manipulate comments as well.

    Kind regards,
    Dennis

14. I loaded the add-in and, much to my dismay, it didn’t work.

    My comments are still the user name with the yellow background.

    I unzipped it, saved it to my desktop, went to Excel. Tools >> Addins, browsed for it, and loaded it.

    What did I miss or do wrong?

15. Never mind….

    I understood “Turn on” to mean that it was turned on and “Turn off” to mean turned off.

    I just think differently.

16. Hi

    I’m with Stacy on this. The Lightbulb is On when the Addin is Off. A tad confusing

    Nice little app though.

    Charles

17. Maybe I should just take the lightbulb out of it then :)

18. I would like to have comments print at the end of each printed page rather than at the end of the entire (printed) worksheet. Anyone know how to accomplish this?

19. I clicked the “X” at the upper right of the Comment Control toolbar, and now I am unable to get the toolbar back, or gain control over the normal Excel comments functions. How do I make the Comment Control toolbar appear again? Help, please. Thanks.

20. Randy,

    You should be able to right click on any other toolbar and see the “Comment Control” toolbar at the bottom. Just click it and you’ll get it back.

21. Hi Juan,
    Possible your add-in does not work under XL97 SR2?

22. Juan,
    I was a bit too early!
    Upon restarting my Excel it seemed to work! Dunno why it didnt work from the start…

    NICE WORK!!!

    May I suggest some enhancements which maybe worth to consider?
    – Apply “New Comment setting” on All already existing Comments (In Selected Range, On Active worksheet, In Workbook)
    – Delete Comments (In selected RAnge, On Active Worksheet, in Workbook)
    – Being able to Change the shape as already suggested before …would be interesting as well!
    -…

    After all it is a Comments-utility!

    Cheers Sige

23. I actually wanted to remove the comment indicator, but the only way seems to be making the background white. This leaves a faint red line on one side though. Has anyone had any success removing the indicator?

24. I need to remove this from my machine as it doesn’t appear to be working correctly. Please help me figure out how to delete, remove, this macro.

25. Nikki: Under Tools > Addins, uncheck the box next to this addin. Also, you might describe the problem you’re having and JPG can probably help.

26. Hi Juan- Very nice utility indeed! Unfortunately i am unable to learn why it only seems to work with the ‘insrt comment’ icon on the ‘Reviewing’ toolbar and not on the right-click drop down in the cells.
    Can you shed some light on the for me? Its cool but would be much more useful if I could open it from a cell. Thanks in advance, I’m sure its just something simple…

27. Nice app. Been trying to find a way to either remove the comment indicators or make them invisible, but retain the comments. This looks like a nice way to achieve this for interactively entered comments, but the Excel application I’m working with is generating the comments via VBA, and the settings in this app do not appear to apply to VBA-generated comments. Any thoughts?

28. The add-in seems to work fine except…I cannot remove the indicator using delete comment. I can only get rid of it by deleting the entire row or column in which it occurs. If I drag the cell to another column , the indicator dissappears, but does not do so if I drag the cell to another row in the same column. When I drag it back to the former column, it re-appears. How do I remove the indicator from cells?

29. Dear Juan,

    Thank you for an excellent way to change comments in Excel. It works perfectly. However, to use it I have to set my Macro security to Medium and click that I agree to run the Macro. I normally have the setting at High. I believe I could leave it at high and not have to click to agree if I could list it as a ‘Trusted Publisher’. I hope with your knowledge of Excel you will be able to tell me how to do this. I am using office 2003.

    Regards,
    David.

30. Why not set the security to medium?

    Alternatively put the code into an add-in, then install the add-in.

31. Dear Ion,

    Thank you very much for the information. Using it as an ‘add-in’ worked perfecly.
    You learn something every day.

    Thanks again,
    Regards,
    David.

32. I downloaded this, added it as an add-in it worked beautifully, btu I also wanted to completely remove the indicator and I don’t believe this fits my needs. When I deleted the file from my desk top(Where I installed it from) I can no longer do anything with comments in any of my Excel workbooks. it says “CommentsControl.xla could not be found…” I unchecked the add-in and I still get that error even in a new workbook, how do I undo what I’ve done??????

33. I searched for such a utility since long. What a great help !!! Many thanks indeed for that xls AddIn. So good to have help among the xls communities… Keep going that way! Thanks again.

34. This is a great add-in for my workflow.
    Here are some headings I currently enter manually, for review tracking:
    Comment heading…
    – Current date [format]
    – Current time [format]
    – Current date/time [format]

35. Hello

    I have problem to remove the Add-in CommentControl.

    I have tried
    – to remove the file from the computer
    – to uninstal it, by uncheck the box in add-in dilog box

    But I get this messige when trying to insert a comment

    ——————————————
    ‘CommentsControl.xla’ could not be found. Check the spelling of the file name, and verify that the file location is correct.

    If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
    ——————————————

    What to do?

    /Eric

36. You should remove the file from the add-ins list before deleting it. If I delete an installed add-in, I get that same message when I open Excel. Then I go to Tools > Addins and uncheck the problem addin. It says it can’t find it and asks if I want to delete it from the list. I say Yes.

37. I have a problem similar to Eric’s above. I would like to go back to not using the macro. How do I remove it? Please someone help me. Thank you.

    Scott

38. This is a brilliant add in, well done!

    Just one Q posted by HD before:

    I want the indicator not to show, if I make the background white a thin red line remains.

    Any ideas?

39. Great add-in. Works like a charm. Added some code so that I could also change the shape of the comment. Thanks for making the code available free of charge.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.