Hi,

I want my own special text (3 lines) inserted in comment box when I right
click "New note"
In Office 2007 I could do that with the vba shown below, but it does not
work in Office 365.
Does someone have a solution that works with Office 365.
I am aware that it is possible to create a complete new button to the right
click menu, but I want to use the existing "New note"

I am running Windows 10 and Office 365

Kind regards,
Kaj Pedersen

This used to work in Office 2007

Insert in Module1
=================

Sub ThisIsMyOwnNote()

Dim cmt As Comment

Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:="This is my own note"
Else
cmt.Text Text:=cmt.Text & Chr(10)
End If
End Sub

----------------------------------------------------------------------------------
Insert in This workbook
=======================

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Insert comment").Reset
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls("Insert comment")
.OnAction = "Testfile.xlsm" & "!ThisIsMyOwnNote"
End With
End Sub

Op zondag 1 maart 2020 11:25:47 UTC+1 schreef :
Hi,

I want my own special text (3 lines) inserted in comment box when I right
click "New note"
In Office 2007 I could do that with the vba shown below, but it does not
work in Office 365.
Does someone have a solution that works with Office 365.
I am aware that it is possible to create a complete new button to the right
click menu, but I want to use the existing "New note"

I am running Windows 10 and Office 365

Kind regards,
Kaj Pedersen

This used to work in Office 2007

Insert in Module1
=================

Sub ThisIsMyOwnNote()

Dim cmt As Comment

Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:="This is my own note"
Else
cmt.Text Text:=cmt.Text & Chr(10)
End If
End Sub

----------------------------------------------------------------------------------
Insert in This workbook
=======================

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Insert comment").Reset
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls("Insert comment")
.OnAction = "Testfile.xlsm" & "!ThisIsMyOwnNote"
End With
End Sub

As far as I know, you cannot use VBA in office365

You must be kidding.
Of course VBA is available and useable in Excel Office 365. I have lots of
other macros working there.

That means I am still hoping for a solution to the problem in question.

Kaj Pedersen

The correct syntax to accomplish what you are trying to do is...

Sub ThisIsMyOwnNote()
Dim cmt As Comment
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
ActiveCell.AddComment "This is my own note"
Else
cmt.Text cmt.Text & Chr(10)
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi, and thank you for trying to solve my Excel problem.
Unfortunately, your suggestion has not helped when I right click and choose
"New note" (I had expected the special text to be inserted).
Instead of getting my special text I get the standard text with my name in
bold as usual. Your VBA works when I run it as a normal macro and so did my
own.

That means the problem is not the vba inserted in module1, but the VBA
inserted in "This workbook" that points to the module 1.
I hope you or somebody else will give it another try.

In fact, my worry is that Microsoft has discontinued the previously working
vba code in Office 365 when they introduced "New note" and "New comment".

Kind regards,
Kaj Pedersen

Hi, and thank you for trying to solve my Excel problem.
Unfortunately, your suggestion has not helped when I right click and choose
"New note" (I had expected the special text to be inserted).
Instead of getting my special text I get the standard text with my name in
bold as usual. Your VBA works when I run it as a normal macro and so did my
own.

That means the problem is not the vba inserted in module1, but the VBA
inserted in "This workbook" that points to the module 1.
I hope you or somebody else will give it another try.

In fact, my worry is that Microsoft has discontinued the previously working
vba code in Office 365 when they introduced "New note" and "New comment".

Kind regards,
Kaj Pedersen

I made a utility named CommentsManager.xla[m] some years back that still works
in 365 as expected. Working code hasn't changed (yet) for the Comment object
even though it has been re-defined in the UI as "Note". In VBA the UI term
"Comment" now refers to "CommentThreaded"! It appears that a cell can't have
both a Comment and a Note at the same time.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you for your information.

Kaj Pedersen

wrote in message
Hi,

I want my own special text (3 lines) inserted in comment box when I right
click "New note"
In Office 2007 I could do that with the vba shown below, but it does not
work in Office 365.
Does someone have a solution that works with Office 365.
I am aware that it is possible to create a complete new button to the
right
click menu, but I want to use the existing "New note"

I am running Windows 10 and Office 365

Kind regards,
Kaj Pedersen

This used to work in Office 2007

Insert in Module1
=================

Sub ThisIsMyOwnNote()

Dim cmt As Comment

Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:="This is my own note"
Else
cmt.Text Text:=cmt.Text & Chr(10)
End If
End Sub

----------------------------------------------------------------------------------
Insert in This workbook
=======================

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Insert comment").Reset
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls("Insert comment")
.OnAction = "Testfile.xlsm" & "!ThisIsMyOwnNote"
End With
End Sub

The "New Note" (for old style comments) and "Insert Comment" (er, for notes)
buttons are stored in a repository type commandbar named "Nil", along with
many others. These get added to the Cell popup when it's shown, but the
original Insert Comment button, the one you've referenced, is not shown.

You can set a reference to the notes button like this:
Set cbCtrl = CommandBars.FindControl(ID:=33158), or directly in the Nil bar.
However changing its OnAction doesn't work and trapping its click event it
never fires, so I suspect a copy of the control is added to the Cell bar
when the popup appears.

Long & short, probably best to add a new button with your own onaction, air
code -
Dim cBar as CommandBar
Dim cbCtl as CommanBarControl
Set cBar = CommandBars("Cell")
on error resume next
Set cbCtl = cBar.Controls("my-button")
On Error Goto 0 ' or error handler
if cbCtl is Nothing Then
Set cbCtl = cBar.ontrols.Add(1, Temporary:=True)
cbCtl.Caption = "my-button"
cbCtl.OnAction= "myMarco"
End If

Peter T