Insert a comment at once in the same cell from grouped worksheets

vladimiratanasiu · Jun 17, 2018

Hello!

Is any other method to insert at a time a comment in the same cell (e.g. A1, B5, F10 etc.) from some grouped sheets of an Excel workbook? If I try to do it in the classic mode, after having grouped the respective sheets all the icons from the Comments section of the Review windows are inactive.
Thank you!

mse330 · Jun 17, 2018

This is my try … Just select the sheets & cell for the comment & run the macro by pressing Alt+F8

Code:

Option Explicit
Sub Comments()

Dim ws As Worksheet, Arr() As String, Comment As String, cell As Range
Dim Counter As Double, i As Double, Sh As String, Comma As String

Set cell = ActiveCell

Counter = 0
For Each ws In ActiveWindow.SelectedSheets
    Counter = Counter + 1
    Sh = Sh & Comma & ws.Name
    Comma = ","
Next

Comment = InputBox("Enter Comment Below ...", "Comment In Multiple Sheets")
ReDim Arr(Counter)

Arr = Split(Sh, Comma)

Sheets(Arr(1)).Select

For i = LBound(Arr) To UBound(Arr)
    Sheets(Arr(i)).Range(cell.Address).AddComment Comment
Next i

For i = LBound(Arr) To UBound(Arr)
    Sheets(Arr(i)).Select (False)
Next i

Erase Arr

End Sub

vladimiratanasiu · Jun 19, 2018

Thank you, it works very well !

Yongle · Jun 19, 2018

@mse330 - that is a neat way to avoid the unwanted comma in string sh

mse330 · Jun 19, 2018

Glad I could help & thank for reporting back ... I just figured out that some lines in the code are not really necessary, in case you are interested shortened version of the code below

Code:

Option Explicit

Sub Comments()

Dim ws As Worksheet, Arr() As String, Comment As String, cell As Range
Dim i As Double, Sh As String, Comma As String

Set cell = ActiveCell

For Each ws In ActiveWindow.SelectedSheets
    Sh = Sh & Comma & ws.Name
    Comma = ","
Next

Comment = InputBox("Enter Comment Below ...", "Comment In Multiple Sheets")
Arr = Split(Sh, Comma)
Sheets(Arr(0)).Select

For i = LBound(Arr) To UBound(Arr)
    Sheets(Arr(i)).Range(cell.Address).AddComment Comment
Next i

For i = LBound(Arr) To UBound(Arr)
    Sheets(Arr(i)).Select (False)
Next i

End Sub

mse330 · Jun 19, 2018

Yongle said:
@mse330 - that is a neat way to avoid the unwanted comma in string sh

That's the beauty of this forum. You learn new things/ideas everyday :wink:

Insert a comment at once in the same cell from grouped worksheets

vladimiratanasiu

Active Member

Excel Facts

mse330

Well-known Member

vladimiratanasiu

Active Member

Yongle

Well-known Member

mse330

Well-known Member

mse330

Well-known Member

Similar threads

Forum statistics

Share this page

Insert a comment at once in the same cell from grouped worksheets

vladimiratanasiu

Active Member

Excel Facts

mse330

Well-known Member

vladimiratanasiu

Active Member

Yongle

Well-known Member

mse330

Well-known Member

mse330

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock