Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Syntax
Marty
try ActiveSheet.DisplayCommentIndicator = ??? -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba syntax | Excel Discussion (Misc queries) | |||
The NOW() syntax | Excel Discussion (Misc queries) | |||
IRR Syntax | Excel Worksheet Functions | |||
Syntax Help | Excel Worksheet Functions | |||
Help with VBA syntax | Excel Programming |