Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 1st 20, 10:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 6
Default My own text using right click "New note"

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

  #2   Report Post  
Old March 1st 20, 03:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 1
Default My own text using right click "New note"

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
  #3   Report Post  
Old March 1st 20, 04:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 6
Default My own text using right click "New note"

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
  #4   Report Post  
Old March 1st 20, 11:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default My own text using right click "New note"

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
  #5   Report Post  
Old March 2nd 20, 08:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 6
Default My own text using right click "New note"

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


  #6   Report Post  
Old March 3rd 20, 01:34 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default My own text using right click "New note"

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
  #7   Report Post  
Old March 3rd 20, 10:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 6
Default My own text using right click "New note"

Thank you for your information.

Kaj Pedersen
  #8   Report Post  
Old March 4th 20, 10:17 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 86
Default My own text using right click "New note"


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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automating Text to Columns -- can I auto-click "OK" on the message robs3131 Excel Programming 2 July 1st 07 02:04 PM
Can I have "Shift-Click" or "Ctrl-Click" Code on Form List? MikeZz Excel Programming 0 June 13th 07 12:58 AM
"Control" plus "click" doesn't allow me to select multiple cells Ken Cooke New Users to Excel 0 September 25th 06 04:46 PM
I have to double click a cell for the "text" format to take Charles Excel Discussion (Misc queries) 2 January 24th 06 02:46 PM
Worksheets("Brackets").DropDowns("Game10").Click Tony_VBACoder Excel Programming 5 January 28th 05 04:41 AM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017