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
Bookmarks