Need help with Syntax

[Marty]

Hello:

I have a workbook which has three worksheets. Each
worksheet has several cells which have comments in them.
I have a toggle button on each of the worksheets which I
want to use to alternately show and hide all of the
comments on the worksheet.

Here is the VBA for the toggle buttons (all three are the
same:

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
Call ShowComments
Else
Call HideComments
End If
End Sub

The macros "ShowComments" and "HideComments" were
recorded. They look like this:

Sub ShowComments()
Application.DisplayCommentIndicator =
xlCommentAndIndicator
End Sub

Sub HideComments()
Application.DisplayCommentIndicator =
xlCommentIndicatorOnly
End Sub

This works, but the problem with this is that each button
shows and hides all of the comments on every worksheet.
I want each button to show and hide the comments only on
the worksheet on which it is located.

I know I need to replace the "Application" in the
recorded macros with some sort of Workbooks
(ActiveWorkbook.Name) and Sheets(ActiveSheet.Name)
combination. I tried this:

Sub ShowComments()
Dim MYSHEET as Object
MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets
(ActiveSheet.Name)
MYSHEET.DisplayCommentIndicator =
xlCommentAndIndicator
End Sub

{and repeat the syntax for the other macro}

but I can't get it to work.

Ideas?

Thanks,
MARTY

 

[mudraker]

Marty


try


ActiveSheet.DisplayCommentIndicator = ???

 

[Marty]

I tried it, it doesn't work. I get a run time error 438 -
"Object doesn't support this property or method."

This is the line it doesn't like:

ActiveSheet.DisplayCommentIndicator =
xlCommentAndIndicator

{I forgot to mention that this is one of the things I had
also tried before. I just recreated it in order to get
the error message right.}

Any other ideas?

 

[Norman Jones]

Hi Marty,

Try:

Sub ToggleButton1_Click()
Dim c As Comment

For Each c In ActiveSheet.Comments
c.Visible = Not (c.Visible)
Next
End Sub

 

[Norman Jones]

Hi Marty,

To show or display all of the comments on the sheet (as opposed to toggling
the display status of all of the individual comments), try:

Sub ToggleButton1_Click()
Dim c As Comment

For Each c In Me.Comments
c.Visible = ToggleButton1.Value
Next

End Sub

 

[Tom Ogilvy]

It appears this property is applied at the application level, not at the
worksheet or workbook level.

 

[Marty]

It works! Thanks Norman!

-----Original Message-----
Hi Marty,

To show or display all of the comments on the sheet (as opposed to toggling
the display status of all of the individual comments), try:

Sub ToggleButton1_Click()
Dim c As Comment

For Each c In Me.Comments
c.Visible = ToggleButton1.Value
Next

End Sub

---
Regards,
Norman





.