Excel - Insert Blank Comment 1

[Petzl]

Hi,

I use comments all the time. Trouble is I have to backspace to remove the name and colon. I understand I can the name gets in there via the tools|options|general tab and selecting user.

What I would like to do though is to be able to right-click on a cell and select Insert Comment and have it come up completely blank.

Is it possible??

Thanks!

 

[sawedoff]

Take a look at this link:

http://support.microsoft.com/?kbid=213766

Sawedoff

 

[Petzl]

Thanks Sawedoff.

This gets me part of the way there and is better than the previous option. I changed one line to "ActiveCell.Comment.Visible = False" but it means I have to click on edit comment still.

Ideally I would still like to put this command on the right click menu as an option and it opens straight away in edit mode.

Asking too much??

Thanks once again...

 

[sawedoff]

I don't know. My knowledge of VBA is limited. Maybe someone in the VBA forum could get you past that barrier.

Sorry.

Sawedoff

 

[combo]

Without error tracing:

ActiveCell.AddComment ""
Application.CommandBars("Reviewing").FindControl(ID:=2056).accDoDefaultAction

combo

 

[Petzl]

Thanks for the response guys.

I get a runtime error 91 on your macro combo. "Object variable or with block variable not set".

Am I doing something wrong??

Thanks!

 

[GlennUK]

Why not do sendkeys to get into cell edit mode? Will try to find out what the cell shortcut commandbar is called so as to let you know how to add your macro call to it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Strange, looks like the 'edit comment' button jumps between commandbars ('Rewieving', 'Cell'), depending on previous actions.
What about this:

On Error Resume Next
ActiveCell.AddComment ""
ErrN = Err.Number
On Error GoTo 0
If ErrN <> 0 Then
    Err.Clear
    MsgBox "Can't add comment"
Else
    Dim cb As CommandBar
    Dim cbc As Office.CommandBarControl
    For Each cb In Application.CommandBars
        Set cbc = cb.FindControl(ID:=2056)
        If Not cbc Is Nothing Then Exit For
    Next cb
    If Not cbc Is Nothing Then
        cbc.accDoDefaultAction
        Set cbc = Nothing
    Else
        MsgBox "Can't edit comment"
    End If
End If

combo

 

[Petzl]

Ok Combo...thanks for the input but you have confused me! What should the whole VB script be for me to setup as a macro?

Thanks!

 

[combo]

Welcome to VBA world (it is not VB scripting!). The code above is more or less complete (one could consider to do some formatting of the comment, as removing bold, change comment's colour, shape, size etc.), you have to put it into macro and choose the way to run it. There is plenty of options here, as:
1. location: standard module, in an add-in or personal macros workbook,
2. the way you run the macro:
- assign a key to run macro, or
- via customize commandbars, add non-standard menu item and assign your macro here, or
- with code, customize the right-click menu ('Cell') - add item and assign macro - the best to pack all in an add-in, with item added/removed every time add-in is opened/closed.

If you are new to VBA, I recommend (except of reading vba help file!) to use personal macros workbook (a hidden workbook that is created when you first choose to record macro here). In Visual Basic Editor (VBE) add/select module and create Sub ... End Sub frame, copy the code to the module into your procedure. In excel interface, go Tools>Macro>Macros find your macro, with "Options.." button assign a key to it.

There is a separate Visual Basic for Applications forum707, however you should know the basics.

combo

 

[Petzl]

I work in IT but programming is another world ;-)

OK...if I put it into a macro and run it it errors with "Compile error: Invalid Outside procedure"

Am I doing things incorrectly??

Thanks!

 

[combo]

Do you have the code within the procedure? Something like:
Sub YourSubNameHere()
...
code here
...
End Sub

Your original problem has no regular excel solution. If you decide to extend excel functionality with macros, you need some basic knowledge on VBA. For more help, read excel help topic on task automation, search for 'macro', see VBA help file. Sawedoff pointed a link to automatic insertion of blank comment, my code turns the comment into edit mode. I sketched some possible solutions on packing it. Your task now is to gain some knowledge on macros and VBA coding.
Sorry for that, as you started the thread in ms office forum which is not targetted on office programming.

combo

 

[Petzl]

Ok....I have it up and running but I when I run the macro it comes up with a box that says "Can't edit comment". I would ideally like the box to just pop up ready for me to enter comments.

Thanks for taking the time!

 

[combo]

What is the office version?

Try to debug the code:
1. See if accDoDefaultAction is available.
Display Object Browser (View > Object Browser). Right-click right pane and mark 'Show Hidden Members' option. Drop-down <All Libraries> combo and select 'Office' library. In the 'classes' pane find 'CommandBarButton', accDoDefaultAction should be visible dimmed on the right.
If you can't see a reference to office library, add it (Tools > References dialog, and 'Microsoft Office x.0' ticked).

2.Find ID of the 'edit comment' button. Start the code in procedure with:

Dim appCBs As Office.CommandBars
Set appCBs = Application.CommandBars

Click on the left margin of code module with line:

If Not cbc Is Nothing Then

the line will get brown with a brown dot on the margin. Now when you run the code it will break here. Go (in VBE) View>Locals window. See if cb or cbc variables point to nothing. All commandbars are under appCBs collection, expand it. 'Item n' means given commandbar, you can expand it again any and have now items describing controls. In excel xp I have Item 28 (commandbar 'Cell') > Item 8 (button for edit comment, ID=2056), it should appear again in 'Reviewing' commandbar (for me item 7, and control item 1).
If the ID is different, change it in code, if not available, try to find it in other commandbars, customise any commandbar and add this button.

combo

 

[GlennUK]

Hi Petzl,

you say:

it comes up with a box that says "Can't edit comment".

As I've already said:

Why not do sendkeys to get into cell edit mode?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.