none
Getting Windows 10 to set default background color for MS Office Comments RRS feed

  • Question

  • When I was using Windows 7, I had no issue as this could be set via the themes and appearances. However, when upgrading to Windows 10, I have not been able to get this done by default, even via the InfoWindow registry settings for both the control panel level and the desktop level set at 255 255 0.

    For one of the workbooks I work in, I have already put in the code line of:

    ActiveCell.Comment.Shape.DrawingObject.Interior.Color = 65535

    So as I don't have to do the setting manually nearly so much.  On the other hand, I am already half-tempted at replacing Excel's F2 keyboard shortcut of inserting a comment at the active cell within Excel with one of my own to have the background color set to what I want to have it set to, so as I don't have to do it manually nearly every time, if not every time.

    Saturday, November 5, 2016 9:09 PM

All replies

  • Hi RRDodge,

    From the description of the thread it looks like you want set the same formatting for all the comments like background color.

    other thing you had mentioned that you want to change the shortcut of adding comments to "F2".

    so you can use the code mentioned below to set the particular format of comments.

    Sub demo()
     Dim MyComments As Comment
     Dim LArea As Long
     For Each MyComments In ActiveSheet.Comments
     With MyComments
     .Shape.AutoShapeType = msoShapeRoundedRectangle
     .Shape.TextFrame.Characters.Font.Name = "Tahoma"
     .Shape.TextFrame.Characters.Font.Size = 8
     .Shape.TextFrame.Characters.Font.ColorIndex = 2
     .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
     .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
     .Shape.Fill.Visible = msoTrue
     .Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
     .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
     End With
     Next
     End Sub
    

    first you need to add all the comments then at the end run the above mentioned macro to set the format.

    if we talk about shortcut the you can press "Shift key + F2 key" to insert comment.

    let me know if you have any further query about this issue.

    you can also try to add comments from the macro with your desired formatting and then assign a short cut key to that macro. so that when you press that shortcut key then comment will be added.

    example code:

    Sub CommentTheCell()
    Application.DisplayAlerts = False
    ActiveCell.ClearNotes
    ActiveCell.NoteText Text:=InputBox("Enter comment")
    ActiveCell.NoteText
    ActiveCell.Comment.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
    End Sub
    
     

    you can also fill solid color instead of gradient.

    visit the link mentioned below to assign shortcut to macro.

    Assigning short-cut keys to VBA macros in Excel

    Note:- it will not allow you to enter "F2" key as a shortcut. you have to select the key with the combination of ctrl key.Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 7, 2016 5:08 AM
    Moderator
  • Hello Deepak,

    very shortly after having posted, I went ahead and created my code as follows:

    Public Sub pcdAddEditComment()
        'Declaration
        Dim l_cmtCurrent As Excel.Comment
        'Initialization
        Set l_cmtCurrent = ActiveCell.Comment
        'Perform actions
        If l_cmtCurrent Is Nothing Then
            ActiveCell.AddComment
            ActiveCell.Comment.Shape.DrawingObject.Interior.Color = 65535
            ActiveCell.Comment.Shape.DrawingObject.Font.Bold = True
           
        End If
        ActiveCell.Comment.Visible = True
        ActiveCell.Comment.Shape.Select True
    End Sub

    I have assigned this to Ctrl-Shift-C.  I could replace the default Shift-F2 function via the Application.OnKey command, but I haven't done so because of the fact, the code doesn't allow for the comment to be automatically to be hidden after done editing, thus I used the following code at the workbook level to address that issue:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Declaration
        Dim l_cmtCurrentCell As Excel.Comment
    'Error Handling Setup
        On Error GoTo ExitSub
        Application.EnableEvents = False
    'Initialization
        Set l_cmtCurrentCell = Target.Resize(1, 1).Comment
    'Execution
        If Not l_cmtCurrentCell Is Nothing Then
            l_cmtCurrentCell.Shape.Left = Target.Offset(0, 1).Left
            l_cmtCurrentCell.Shape.Top = Target.Top
            l_cmtCurrentCell.Visible = False
        End If
    ExitSub:
        On Error GoTo 0
        Application.EnableEvents = True
    End Sub

    So now, I press Ctrl-Shift-C to create the comment, then I put in the comment.  After that, I press the Esc key once, and if need to make adjustments to the comment (i.e., comment size), press the Menu key, O, Page Down 3 times, then make my adjustments as needed.  I then press Enter.  At this point, regardless if I needed to make the adjustments or not, I press the Esc key, then I press the up arrow, then the down arrow to trigger the ChangeSelection event to hide the comment.

    Your solution seems to want to address all comments after the creation, as opposed to addressing the issue during the creation process to the specific comment that's being created, which is a huge difference in processing time, especially when you are dealing with more than 45k comments and counting within the worksheet.  By the time I am done with the worksheet, there could be roughly 85k comments in it.

    There are other comment issues, which I have noted, but won't deal with until other aspects of this big project is completed, such as the size of the comments being increased/decreased as rows/columns are inserted/deleted.  Some aspects of comments also already addressed within the ChangeSelection event, such as the location of the comment being moved as rows/columns are inserted/deleted, though you only notice this issue (that is without the above fix in place), when you go to edit the comment.  Also, the option to set the defaults for new comments based on a particular comment as shown in the shortcut context menu (when in edit mode of a comment, press the Esc key, then the Menu key to the right of the spacebar) doesn't seem to work for creating new comments either, thus another reason why I have had to go this route.  Wish that route worked as you would expect it to work, but it doesn't.  If that route would work as one would expect it to work, about 95% of these comment issues could be addressed once and for all, including the resizing and relocation of comments issue.

    One other thing I am keeping note of, rather if I will run into the same type of issue as I have with the number of defined names within a single workbook.  With the number of defined names within a single workbook, once you surpass 32,767 defined names, the workbook properties can become unstable.  If you exceed 65,536 defined names within the workbook, save it with the excess over 65,536 defined names, and close it out with the excess, the next time you open it up, Dr. Watson comes up, REMOVES EVERYTHING except for the data and formulas from the workbook.  It removes all formats including fonts and styles, it removes all charts, all VBA code, and whatever else.  Because of me having ran into that issue with Excel, it makes me wonder if I will run into that same issue with comments.  So far, I haven't seen the issue with the soft-code limit of 32,767 for the comments as I did with the defined names (the Name Object within the Names Collection Object on the Workbook Object), but that 65,536 definitely came across as a hard-code limit, acting as if the Count property/attribute of the Names collection object is only a signed integer data type, as opposed to the long data type that the help file specification says what the property/attribute is.  The help file doesn't specify though rather if it is signed or unsigned.

    • Edited by RRDodge Friday, November 18, 2016 6:12 AM
    Friday, November 18, 2016 5:56 AM
  • Hi RRDodge,

    in the original post you had mentioned that you had issue with comment background color and shortcut key to create comment.

    but in your last post you had mentioned that you had huge amount of comments in your workbook.

    I don't know that which MS Office version you are using may be (MS Office 2016).

    how much total data you have in your workbook?

    may be more then the comments. it's like you want to add comment for each data.

    and want individual formatting for every comment.

    so you can use the cell address to find it contains the comment or not and if it contains comment then do the formatting to that particular comment.

    by this way the execution time will be saved.

    Sub Test()
    Dim varComment As String
    Dim c As Comment
    With Range("A1")
        On Error Resume Next
        Set c = .Comment
        On Error GoTo 0
        If c Is Nothing Then
            MsgBox "No comment", vbExclamation
        Else
            varComment = c.Text
            MsgBox varComment, vbInformation
        End If
    End With
    End Sub

    but as you had mentioned there are 85K Comments are there.

    if you perform this operation on 1 comment code will execute fast but if there are so many comments on which you want to apply the same formatting it will take time to perform this operation.

    so you need to rethink about comments.

    does it necessary for you to create this much huge comment?

    if yes, then you can find work around for this issue.

    like you can create a "Comment" column beside your data column.

    if cell has no comment then you can store "N/A" in the Comment cell.

    it will work fast then comments.

    so it's all upon to you and based on your requirement you can select the best suitable way for your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 21, 2016 1:36 AM
    Moderator
  • The comments are all the same format, though given the number of comments, I am paying attention to that given the Dr. Watson issue I ran into when I had more than 65,536 defined names within a single workbook saved and closed, which removed everything except for formulas and data values the next time the workbook was opened.  That happened in Excel 2002 (aka Excel XP)

    Just saying, when I was using Windows 7, I didn't have to have any of this format stuff in code because I was able to have the format stuff set via the appearance and themes within the display settings of the control panel.  However, Windows 10 did away with all of this and even when you change the registry settings, the next time you reboot the system, Windows 10 resets those registry settings back to the default settings, which has forced me to have to code all of this stuff for when creating new comments such as via the Shift-F2 keyboard shortcut.

    Sincerely,

    RRDodge, CPA, Security+

    Tuesday, October 24, 2017 1:45 PM
  • Hi RRDodge,

    you had posted your reply after almost a year.

    you had mentioned that this was happened with Excel 2002.

    do you mean you are using Excel 2002 on windows 10?

    here, I want to inform you that support for Excel 2002 was already ended.

    I suggest you to upgrade your Office suite to the latest version of Office which also works best with Windows 10 and provides lot more features and functionality then Excel 2002.

    so many things are changed, added, removed, updated in Windows 10 and new Office suite.

    it also can be the reason to cause this issue.

    please inform me and correct me if I misunderstand something in your above description.

    I will try to correct my self and try to provide you a suggestion that might help you to solve your issue.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 25, 2017 3:23 AM
    Moderator
  • The exceeding of 65,536 defined names saved and closed in a workbook is what happened with Excel 2002.  However, the background color (or more like not being able to set the color and fonts within Windows Themes) started with Windows 10, as I was able to set those settings in Windows 7.

    The only reason why I mentioned about the number of defined names issue even though according to specification files, the number of defined names is only limited by main memory (RAM), I am also putting in a large number of comments into my workbook and wonder if I will end up facing the same sort of issue as I did with the defined names.  As such, they are 2 different issues (no longer able to set windows fonts and color themes via Display settings in Windows 10 vs the workbook not capable of retaining more than 65,536 defined names, which happened in Excel 2002.

    Sincerely,

    RRDodge, CPA, Security+

    Wednesday, October 25, 2017 1:33 PM
  • Hi RRDodge,

    as I informed you before that support for Excel 2002 had already ended.

    also we are not available with Excel 2002 version to make a test and reproduce the issue.

    if you are available with any of the higher version then Office 2003 (e.g. from Office 2007 to Office 2016) then we can provide support for that.

    so you can try to reproduce the issue in higher version of Office and if you are able to reproduce the same issue then kindly inform us about that.

    we will try to test the issue on our side.

    if the solution or any work around is available then we will try to provide you.

    if it is a bug in Excel then we can try to provide a link to submit the feedback regarding the bug.

    then Excel developer team will work on the issue and try to provide fix in future updates.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 26, 2017 1:13 AM
    Moderator
  • So you saying that you wont even look into why with Windows 10, we can no longer have the various fonts and colors set for the various windows elements (i.e., font style and size, app background and foreground color, other background and foreground colors) as we could with Windows 7, thus why I have had to do all of this extra coding in Excel 2013 when I had upgraded to Windows 10?, as what this thread was originally for?

    As to the Defined Names issue, I could very easily create VBA code in Excel and test for that issue in Excel 2013, as that's how I ran into that issue in 2002.  As I stated before, I only made reference this defined names issue because of the large number of comments that will be not just in the workbook, but in the worksheet (since comments are stored at the worksheet level, not the workbook as defined names are stored at; as shown in the object model).

    Sincerely,

    Ronald R. Dodge, Jr., CPA, Security+

    • Edited by RRDodge Thursday, October 26, 2017 2:56 PM
    Thursday, October 26, 2017 2:50 PM
  • Hi RRDodge,

    can you share your Excel file that you created in Excel 2013 and that can able to produce the issue for defined names on our side?

    we will try to test it.

    as this thread is old, it is possible that you made some changes in code during this 1 year.

    so it is better if you provide us a latest file.

    if we talk about the font style and size, app background and foreground color, other background and foreground colors. then I can see that you said that you can able to made changes in windows 7 but you are not able to use these features in Windows 10.

    so here , I want to confirm with you that did you find this behavior with Excel 2013?

    if yes, please also provide steps to reproduce the issue.

    we will also make a test with it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 27, 2017 1:48 AM
    Moderator