Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: User Name in Comments

  1. #11
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    teebruin,

    to make you happy

    Ok goto Thisworkbook object in VBA and paste this code in module view:

    Private Sub Workbook_Open()
    Application.CommandBars("Insert").Controls("Comment").OnAction = "ChangeCommentAction"
    Application.CommandBars("Cell").Controls("Insert Comment").OnAction = "ChangeCommentAction"
    End Sub

    Then insert a module related workbook and paste this code:

    Private Sub ChangeCommentAction()
    ActiveCell.AddComment ""
    ActiveCell.Comment.Visible = True
    ActiveSheet.Shapes(ActiveCell.Comment.Shape.Name).Select
    End Sub

    This will work for you. If you want to get your old Comment or Insert Comment actions then just goto Immediate window and execute this lines:

    Application.CommandBars("Insert").Controls("Comment").OnAction=""
    Application.CommandBars("Cell").Controls("Insert Comment").OnAction=""

    or

    Application.CommandBars("Insert").Reset
    Application.CommandBars("Cell").Reset


    Regards

  2. #12
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Question?
    Suat

  3. #13
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops!

    I am sorry teebruin,

    It is still in VBA
    But the exact solution i think.


  4. #14
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 11:53, nancyo wrote:
    On the toolbar, insert comment will bring up a coment box on the excel spreadsheet (in a specific cell). Simply backspace or highlight and delete the user name.
    Hi Nancy,

    I appreciate the assistance. I am aware I can backspace the header out, and I know I can highlight and delete it.

    My point is that there really should be a way to avoid having to do that at all. Take for example one is preparing a "professional" document which contains many comments. Even having a " :" remaining can make the document "appear" unprofessional.

    It is a function of the program that some programmer assumed everyone would want or like, but obviously they were mistaken if people are constantly having to delete it manually.

    I am getting the feeling that this will be a fact of life whilst doing this the non-VBA way, thus I would be interested in learning the VBA method of inserting a comment.

    Mike

  5. #15
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mike,

    Can you apply the code i wrote above or would you like me to explain how you will do that or send you a sample workbook?

    It is exactly does what you want.




    _________________
    Oz ~ TheWordExpert

    [ This Message was edited by: smozgur on 2002-04-23 12:22 ]

  6. #16
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 12:20, smozgur wrote:
    Can you apply the code i wrote above or would you like me to explain how you will do that or send you a sample workbook?

    It is exactly does what you want.
    I played around with it very quickly, however, the nuances of doing this elude me. I would need essentially a step by step, click by click walkthrough, as I have had absolutely no experience with VBA.

    I can for the most part follow the logic behind the code (for the most part), however, it is the editor and how it relates to my current document, as well as, where one properly inserts your code where I am stumbling.

    Thanks for the continued help Smozgur.

    Mike

  7. #17
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mike,

    Actually the code above had a bug but i solved it now.

    1- Open a new workbook.
    2- Click Tools_Macro_Visual Basic Editor (or Alt+F11) to open VBA. You are in VBA editor now.
    3- Click View_Project Explorer (or Ctrl+R) to see Project explorer.
    4- You will see VBAProject Object and Microsoft Excel Objects under this item.
    5- Double click on Microsoft Excel Objects. You will see 3 sheet object (depends on your new workbook sheet count) and definetely one workbook object named as Thisworkbook.
    6- Double click on Thisworkbook object then you will see thisworkbook module window. It will say General and Declarations in little comboboxes at the top of that window.
    7- Paste these two codes there:

    'This first code set OnAction property for right click menu and main menu Insert Comment function. OnAction property is always empty as default because Excel functions does internal jobs. But we can change them by using OnAction property. Now our two menu items (Comment in Insert menu and Insert Comment in Right click menu on cells) will run the "ChangeCommentAction" function which we will write soon.

    Private Sub Workbook_Open()
    Application.CommandBars("Insert").Controls("Comment").OnAction = "ChangeCommentAction"
    Application.CommandBars("Cell").Controls("Insert Comment").OnAction = "ChangeCommentAction"
    End Sub

    'This code will set your Comment functions as default. It means when you try to close this workbook then they will stop running that macro and use default actions. This is important, you should do this (until you decide to use this macro and let me tell you how you would put it in your personal workbook to run for all your documents) because if we didnot set the default actions then you will get some errors because you closed the workbook which has macro.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Insert").Controls("Comment").OnAction = ""
    Application.CommandBars("Cell").Controls("Insert Comment").OnAction = ""
    End Sub

    8- Now goto menu and Insert_Module and you will see that you inserted a new module which will keep our main macro. You should see that new code window opened for Module1. Just paste the code below in it.

    'This code run by Insert Comment in right click menu or Insert_Comment menu. As you can read in english in code: it Add a Comment into the ActiveCell and then it set its text as null ("", which you want) and then make it visible to select and let you write comments in it.

    Private Sub ChangeCommentAction()
    ActiveCell.AddComment ""
    ActiveSheet.Shapes(ActiveCell.Comment.Shape.Name).Visible = msoCTrue
    ActiveSheet.Shapes(ActiveCell.Comment.Shape.Name).Select
    End Sub

    9- Save your workbook and close it then reopen. Now you can be happy i hope. (Dont worry if you dont see the cursor is not blinking in comment area, just start typing when you see it)
    10- If you are having troubles please let me know to send you a sample xls file to see and at least be able to decide to apply or not.

    Regards

    _________________
    Oz ~ TheWordExpert

    [ This Message was edited by: smozgur on 2002-04-23 14:27 ]

  8. #18
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Smozgur,

    Worked like a charm!

    Thank you for all your help!

    Mike

  9. #19
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice to hear that

    Suat

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •