+ Reply to Thread
Results 1 to 4 of 4

VBA Customized Comment format

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    VBA Customized Comment format

    Good Evening!

    I'm wondering if there is a possibility of micro VBA command that can automatically work to set a specific color and font size and box size on every comment box that I might be adding on any cell on a protected sheet.

    because I'm working on a protected worksheet and from time to time we keep adding comments, so for that I need the above action, to avoid removing the protection of the worksheet just for the purpose of adding comments on cells, and I also want to have the color and text size fixed directly to save the time of editing the comment box. and this all should work automatically upon adding new comment while the sheet is protected.

    I hope I can find a solution to this task.

    thank you in advance.


    currently, I'm using the following VBA but I don't know the option to customize the color inside the box, e.i. the user name in the comment should be in different color "Red" and the text inside the box in another color like "Blue" and the text size should be "10" always.

    Sub SetCommentsProperties()
    Dim Cell As Range
    For Each Cell In Selection
    If Not Cell.Comment Is Nothing Then
    With Cell.Comment.Shape.TextFrame.Characters.Font
    .ColorIndex = 3
    .Size = 12
    .Name = "Arial Black"
    End With
    End If
    Next Cell
    End Sub

    On the same sheet, I have added one more VBA button to allow me to add/delete comments in the protected sheet, but the desired look of the comment box is not changing when the worksheet is protected, because the module of the comment box is not working automatically.

    I have attached a screenshot and the workbook also for additional clarification.

    THE PASSWORD OF THE WORKBOOK IS "1479"

    I hope to find a solution to this matter.

    11111.pngBook1111.xlsm

  2. #2
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: VBA Customized Comment format

    Hi there,

    Try this:

    Please Login or Register  to view this content.
    Leo Skywalker
    May the force be with you.

  3. #3
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: VBA Customized Comment format

    @ LeoSkywalker this is brilliant brother and it’s work perfectly . Thank you so much

  4. #4
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: VBA Customized Comment format

    You're welcome!, glad that I could help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if cell in range is empty, if it is delete comment else format comment to
    By banaanas in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2015, 01:15 AM
  2. Customized Number Format Required.
    By aley.ali in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-14-2013, 06:33 AM
  3. Replies: 11
    Last Post: 08-16-2012, 09:49 AM
  4. Customized Layout/format for Pivot table
    By aicheng in forum Excel General
    Replies: 0
    Last Post: 08-16-2011, 01:03 AM
  5. [SOLVED] Customized Date Format
    By Frustrated in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2005, 07:05 PM
  6. Replies: 1
    Last Post: 09-19-2005, 03:05 PM
  7. [SOLVED] Customized VBA Editor Format Save Location
    By Jerry N in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-12-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1