Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
I am trying to make the comments I add to cells in my worksheet appear when I
tab or click in a cell, as opposed to having to point with the mouse. For various reasons, data validation message boxes don't work (limitations in number of characters, etc.) Here is my code (I am NOT experienced in VBA, which you can tell). I keep getting errors. I want to say: In the active sheet (or workbook, or even range!) when I click or tab to a cell, display the comment. ub DisplayComments() If activecell.comment Is Nothing Then 'do nothing Else comment.Visible = True End If Thanks for your help! -- Thanks! Dee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Put this in worksheet code:
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 REMEMBER: worksheet code, not a standard module. -- Gary''s Student - gsnu200720 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
One way:
Put this in your worksheet code module: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim cm As Comment Static bVisibleComment As Boolean If bVisibleComment Then For Each cm In ActiveSheet.Comments cm.Visible = False Next cm End If If Not ActiveCell.Comment Is Nothing Then ActiveCell.Comment.Visible = True bVisibleComment = True End If End Sub In article , dee wrote: I am trying to make the comments I add to cells in my worksheet appear when I tab or click in a cell, as opposed to having to point with the mouse. For various reasons, data validation message boxes don't work (limitations in number of characters, etc.) Here is my code (I am NOT experienced in VBA, which you can tell). I keep getting errors. I want to say: In the active sheet (or workbook, or even range!) when I click or tab to a cell, display the comment. ub DisplayComments() If activecell.comment Is Nothing Then 'do nothing Else comment.Visible = True End If Thanks for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Thank you for your response. I tried this, but it didn't seem to work. I
must be doing something wrong. -- Thanks! Dee "JE McGimpsey" wrote: One way: Put this in your worksheet code module: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim cm As Comment Static bVisibleComment As Boolean If bVisibleComment Then For Each cm In ActiveSheet.Comments cm.Visible = False Next cm End If If Not ActiveCell.Comment Is Nothing Then ActiveCell.Comment.Visible = True bVisibleComment = True End If End Sub In article , dee wrote: I am trying to make the comments I add to cells in my worksheet appear when I tab or click in a cell, as opposed to having to point with the mouse. For various reasons, data validation message boxes don't work (limitations in number of characters, etc.) Here is my code (I am NOT experienced in VBA, which you can tell). I keep getting errors. I want to say: In the active sheet (or workbook, or even range!) when I click or tab to a cell, display the comment. ub DisplayComments() If activecell.comment Is Nothing Then 'do nothing Else comment.Visible = True End If Thanks for your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Thank you so much. It worked like a charm. No need to pull the rest of my
hair out! :-) -- Thanks! Dee "Gary''s Student" wrote: Put this in worksheet code: 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 REMEMBER: worksheet code, not a standard module. -- Gary''s Student - gsnu200720 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
I have another question. I've just been testing your code and it works very
well. However, it seem that now whenever I click in a cell, the hourglass is displayed for a few seconds. Is there any way to stop this? I'm not sure why it's doing it... -- Thanks! Dee "Gary''s Student" wrote: Put this in worksheet code: 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 REMEMBER: worksheet code, not a standard module. -- Gary''s Student - gsnu200720 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
The hour glass probably comes from:
r.Comment.Visible = False this line restores the comments to hidden when you leave the cell. If you remove this line the routine will run much faster, but the comments will remain visible when the cell is left. -- Gary''s Student - gsnu20072 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Got it. I've been spending a lot of time trying to figure this code out and
think I have part of it. If you have time, would you mind looking at my comments to tell me if I'm totally off track? There are xxx s where I don't have a clue! Thank you. Private Sub Worksheet_SelectionChange(ByVal Target As range) 'Private tells VBA to hide the sub from other programming elements (sub will not appear in macro list either) 'Worksheet_SelectionChange tells the sub to "fire" whenever a cell is selected in the worksheet 'ByVal Target As range Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) 'Sets range as being on the active sheet, cells on worksheet being used, display cell comment r.comment.Visible = False 'Restores the comments status back to hidden when you leave the cell If Intersect(Target, r) Is Nothing Then Exit Sub 'If xxxx , then exit the sub End If Target.comment.Visible = True 'Otherwise, when move to cell, display any comments End Sub -- Thanks! Dee "Gary''s Student" wrote: The hour glass probably comes from: r.Comment.Visible = False this line restores the comments to hidden when you leave the cell. If you remove this line the routine will run much faster, but the comments will remain visible when the cell is left. -- Gary''s Student - gsnu20072 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
You are right on track !! The intersect thing just compares the currently
selected cell with the collection of cells that have comments. If the currently selected cell is not part of the collection, then the currently selected cell has no comments in it and the routine can exit. -- Gary''s Student - gsnu200720 "dee" wrote: Got it. I've been spending a lot of time trying to figure this code out and think I have part of it. If you have time, would you mind looking at my comments to tell me if I'm totally off track? There are xxx s where I don't have a clue! Thank you. Private Sub Worksheet_SelectionChange(ByVal Target As range) 'Private tells VBA to hide the sub from other programming elements (sub will not appear in macro list either) 'Worksheet_SelectionChange tells the sub to "fire" whenever a cell is selected in the worksheet 'ByVal Target As range Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) 'Sets range as being on the active sheet, cells on worksheet being used, display cell comment r.comment.Visible = False 'Restores the comments status back to hidden when you leave the cell If Intersect(Target, r) Is Nothing Then Exit Sub 'If xxxx , then exit the sub End If Target.comment.Visible = True 'Otherwise, when move to cell, display any comments End Sub -- Thanks! Dee "Gary''s Student" wrote: The hour glass probably comes from: r.Comment.Visible = False this line restores the comments to hidden when you leave the cell. If you remove this line the routine will run much faster, but the comments will remain visible when the cell is left. -- Gary''s Student - gsnu20072 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
OK, I've been going over the code after your response (thank you!) and what I
don't understand is the order in which the code is being executed. It looks as though the last line of code says to display the comment, but the code above it says to hide the comment when leaving the cell. Also, does r mean range? I've been researching and can't seem to find something that clearly explains this... BTW, I sure see your name out there! Thank you so much! -- Thanks! Dee "Gary''s Student" wrote: You are right on track !! The intersect thing just compares the currently selected cell with the collection of cells that have comments. If the currently selected cell is not part of the collection, then the currently selected cell has no comments in it and the routine can exit. -- Gary''s Student - gsnu200720 "dee" wrote: Got it. I've been spending a lot of time trying to figure this code out and think I have part of it. If you have time, would you mind looking at my comments to tell me if I'm totally off track? There are xxx s where I don't have a clue! Thank you. Private Sub Worksheet_SelectionChange(ByVal Target As range) 'Private tells VBA to hide the sub from other programming elements (sub will not appear in macro list either) 'Worksheet_SelectionChange tells the sub to "fire" whenever a cell is selected in the worksheet 'ByVal Target As range Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) 'Sets range as being on the active sheet, cells on worksheet being used, display cell comment r.comment.Visible = False 'Restores the comments status back to hidden when you leave the cell If Intersect(Target, r) Is Nothing Then Exit Sub 'If xxxx , then exit the sub End If Target.comment.Visible = True 'Otherwise, when move to cell, display any comments End Sub -- Thanks! Dee "Gary''s Student" wrote: The hour glass probably comes from: r.Comment.Visible = False this line restores the comments to hidden when you leave the cell. If you remove this line the routine will run much faster, but the comments will remain visible when the cell is left. -- Gary''s Student - gsnu20072 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Because the code does not know which comments are hidden and which comments
are visible the: r.comment.Visible = False makes EVERY comment on the sheet hidden. The last part of the code reveals only the selected cell's comment. -- Gary''s Student - gsnu200720 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! PLS!
Hi again. I tested this again at length today and find that some of my
comments stay displayed, even when I move to another cell. Then, after awhile I get: Run-time error '91': Object variable or With block variable not set Any help would be GREATLY appreciated! -- Thanks! Dee "Gary''s Student" wrote: Because the code does not know which comments are hidden and which comments are visible the: r.comment.Visible = False makes EVERY comment on the sheet hidden. The last part of the code reveals only the selected cell's comment. -- Gary''s Student - gsnu200720 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|