Need help with Syntax
 

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

Need help with Syntax
 

Marty


try


ActiveSheet.DisplayCommentIndicator = ???

--
Message posted from http://www.ExcelForum.com

Need help with Syntax
 

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?

-----Original Message-----
Marty


try


ActiveSheet.DisplayCommentIndicator = ????


---
Message posted from http://www.ExcelForum.com/

.

Need help with Syntax
 

Hi Marty,

Try:

Sub ToggleButton1_Click()
Dim c As Comment

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

---
Regards,
Norman


"Marty" wrote in message
...
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

Need help with Syntax
 

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


"Norman Jones" wrote in message
...
Hi Marty,

Try:

Sub ToggleButton1_Click()
Dim c As Comment

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

---
Regards,
Norman


"Marty" wrote in message
...
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

Need help with Syntax
 

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

--
Regards,
Tom Ogilvy

"Marty" wrote in message
...
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?

-----Original Message-----
Marty


try


ActiveSheet.DisplayCommentIndicator = ????


---
Message posted from http://www.ExcelForum.com/

.

Need help with Syntax
 

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


"Norman Jones" wrote
in message
...
Hi Marty,

Try:

Sub ToggleButton1_Click()
Dim c As Comment

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

---
Regards,
Norman


"Marty" wrote in
message
...
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




.