User Name in Comments

[mikeiwo]

I am trying to get rid of the automatic username placed at the top of my comments. I have played around with the User Name designation in the tools|options|General section but the best I can come up with is putting a "space" the input box.

This of course doesn't solve the problem it merely leaves me with a " :" at the start of my comment.

Is there any way of disabling this or is it something I am just going to have to live with. For some reason I find most annoying.

Thanks in advance for the assistance.

Mike

 

[smozgur]

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

 

[smozgur]

Question?

 

[smozgur]

Oops!

I am sorry teebruin,

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

 

[mikeiwo]

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

 

[smozgur]

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

 

[mikeiwo]

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

 

[smozgur]

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

 

[mikeiwo]

Hi Smozgur,

Worked like a charm!

Thank you for all your help!

Mike

 

[smozgur]

Nice to hear that