| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
Hi,
I was working with some code this week and, after some very kind help, ended up with the following. My objective is to display comments in the worksheet when click or otherwise activate a cell that contains comments. (I don't want the users to have to point to the cell to see the comment.) The problems a 1. When one of the users runs the code, he gets a runtime error 91 and the code only works for one comment. 2. When I run the code, it seems to work for only one comment. When I tab to other cells that contain comments, it displays them, but leaves them displayed when I leave the cell instead of hiding them. Any help would be GREATLY appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) r.Comment.Visible = False If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Thanks! Dee |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
Did you try the alternative event macro that I suggested?
In article , dee wrote: Any help would be GREATLY appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
This event is only activated when you select a new cell.
Try this. I've added a couple of debug.prints to figure out the addresses of the ranges. Every time the comment was changed from visible to not visible it triggered a change event. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) r.Comment.Visible = False Debug.Print r.Address, Target.Address If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True Application.EnableEvents = True End Sub "dee" wrote: Hi, I was working with some code this week and, after some very kind help, ended up with the following. My objective is to display comments in the worksheet when click or otherwise activate a cell that contains comments. (I don't want the users to have to point to the cell to see the comment.) The problems a 1. When one of the users runs the code, he gets a runtime error 91 and the code only works for one comment. 2. When I run the code, it seems to work for only one comment. When I tab to other cells that contain comments, it displays them, but leaves them displayed when I leave the cell instead of hiding them. Any help would be GREATLY appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) r.Comment.Visible = False If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Thanks! Dee |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
We need to loop the hide part:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) For Each rr In r rr.Comment.Visible = False Next If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Gary''s Student - gsnu200720 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
Yes, I did. I had sent you a reply, but maybe you didn't see it? It didn't
seem to do anything. Probably something I'm doing. wrong. -- Thanks! Dee "JE McGimpsey" wrote: Did you try the alternative event macro that I suggested? In article , dee wrote: Any help would be GREATLY appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
Hi Gary! Thanks again. I tried this, but now the comments don't show when I
activate the cell... -- Thanks! Dee "Gary''s Student" wrote: We need to loop the hide part: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) For Each rr In r rr.Comment.Visible = False Next If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Gary''s Student - gsnu200720 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
Hi again,
I exited Excel and then re-tried your code and it worked! Thank you! Why would this make a difference? -- Thanks! Dee "Gary''s Student" wrote: We need to loop the hide part: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) For Each rr In r rr.Comment.Visible = False Next If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Gary''s Student - gsnu200720 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
My deepest apologies. I exited Excel and reloaded it, then re-tried your
code, which worked perfectly. Thank you so much. -- Thanks! Dee "JE McGimpsey" wrote: Did you try the alternative event macro that I suggested? In article , dee wrote: Any help would be GREATLY appreciated. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
I don't know.
I also don't know why I had to use a loop instead of just hiding all the comments with one swell foop! -- Gary''s Student - gsnu200720 "dee" wrote: Hi again, I exited Excel and then re-tried your code and it worked! Thank you! Why would this make a difference? -- Thanks! Dee "Gary''s Student" wrote: We need to loop the hide part: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) For Each rr In r rr.Comment.Visible = False Next If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Gary''s Student - gsnu200720 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!
Thanks for your response. I have everything figured out now.
-- Thanks! Dee "Barb Reinhardt" wrote: This event is only activated when you select a new cell. Try this. I've added a couple of debug.prints to figure out the addresses of the ranges. Every time the comment was changed from visible to not visible it triggered a change event. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) r.Comment.Visible = False Debug.Print r.Address, Target.Address If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True Application.EnableEvents = True End Sub "dee" wrote: Hi, I was working with some code this week and, after some very kind help, ended up with the following. My objective is to display comments in the worksheet when click or otherwise activate a cell that contains comments. (I don't want the users to have to point to the cell to see the comment.) The problems a 1. When one of the users runs the code, he gets a runtime error 91 and the code only works for one comment. 2. When I run the code, it seems to work for only one comment. When I tab to other cells that contain comments, it displays them, but leaves them displayed when I leave the cell instead of hiding them. Any help would be GREATLY appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) r.Comment.Visible = False If Intersect(Target, r) Is Nothing Then Exit Sub End If Target.Comment.Visible = True End Sub -- Thanks! Dee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
