Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comments
Wow! I created two macros (ResetComments and ResizeComments) and they are
awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson |
#2
|
|||
|
|||
I think the simplest solution would be to only run the macro when all the rows
are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson |
#3
|
|||
|
|||
I made a mistake in a different post and the same mistake he
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#4
|
|||
|
|||
Still doesn't work, but you said "If you want, you could add this section
before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
I meant before the "for/next" group of statements (or just before the "For Each"
line): Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub piddilin wrote: Still doesn't work, but you said "If you want, you could add this section before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
I copied and replaced your code (below) with everything above Sub
Comments_AutoSize() but I'm afraid it still doesn't work but I'm not going to waste any more of your valuable time. I'll live with it - maybe someday I learn code and fix it myself but thanks so much for your time and patience. Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With "Dave Peterson" wrote: I meant before the "for/next" group of statements (or just before the "For Each" line): Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub piddilin wrote: Still doesn't work, but you said "If you want, you could add this section before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
I wouldn't give up.
If you still want to try to fix the problem, then you'll have to share what "doesn't work" means. The only change to that first routine was to unhide the filtered rows. You could do that manually, too. (Data|filter|Showall) piddilin wrote: I copied and replaced your code (below) with everything above Sub Comments_AutoSize() but I'm afraid it still doesn't work but I'm not going to waste any more of your valuable time. I'll live with it - maybe someday I learn code and fix it myself but thanks so much for your time and patience. Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With "Dave Peterson" wrote: I meant before the "for/next" group of statements (or just before the "For Each" line): Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub piddilin wrote: Still doesn't work, but you said "If you want, you could add this section before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Thanks, I'll hang in here as long as you can stand it.
The code I copied addresses two issues (a) the Edit Comment textbox appearing next to the active cell, and (b) exposing all the words (instead of the text box being resized so that you can't see all the words). It's the first issue (a) that stops working if I'm in AutoFilter. In other words, when I right click a cell and select Edit Comment, the textbox shows up somewhere in the hinderlands of the spreadsheet instead of right next to the active cell. Maybe it's that I'm not setting it up properly: Here's my steps, from my spreadsheet, I open MS Visual Basic screen with ALT-F11, paste in the code File Close & Return to MS Excel from the spreadsheet click CTRL+F8, select each Go back to Excel select Comments_AutoSize Run CTRL+F8, select Reset Comments Run. Then I auto filter out just one date and right-click a cell with a comment flag, in this example H762 select Edit Comment and the textbox ends up on row 1645. Hope this helps, Peggy "Dave Peterson" wrote: I wouldn't give up. If you still want to try to fix the problem, then you'll have to share what "doesn't work" means. The only change to that first routine was to unhide the filtered rows. You could do that manually, too. (Data|filter|Showall) piddilin wrote: I copied and replaced your code (below) with everything above Sub Comments_AutoSize() but I'm afraid it still doesn't work but I'm not going to waste any more of your valuable time. I'll live with it - maybe someday I learn code and fix it myself but thanks so much for your time and patience. Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With "Dave Peterson" wrote: I meant before the "for/next" group of statements (or just before the "For Each" line): Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub piddilin wrote: Still doesn't work, but you said "If you want, you could add this section before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Ok, I think I have it.
If your data is unfiltered, then the comments show up nicely--both when hovering over the cell and when editting. But if you filter the range, then the comment is ok when you hover over the cell--but not when you edit the comment. How about just fixing the comments that are associated with visible cells? Option Explicit Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments If cmt.Parent.EntireRow.Hidden Then 'do nothing Else cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 End If Next cmt End Sub This worked until I did a showall or filtered using a different criteria. piddilin wrote: Thanks, I'll hang in here as long as you can stand it. The code I copied addresses two issues (a) the Edit Comment textbox appearing next to the active cell, and (b) exposing all the words (instead of the text box being resized so that you can't see all the words). It's the first issue (a) that stops working if I'm in AutoFilter. In other words, when I right click a cell and select Edit Comment, the textbox shows up somewhere in the hinderlands of the spreadsheet instead of right next to the active cell. Maybe it's that I'm not setting it up properly: Here's my steps, from my spreadsheet, I open MS Visual Basic screen with ALT-F11, paste in the code File Close & Return to MS Excel from the spreadsheet click CTRL+F8, select each Go back to Excel select Comments_AutoSize Run CTRL+F8, select Reset Comments Run. Then I auto filter out just one date and right-click a cell with a comment flag, in this example H762 select Edit Comment and the textbox ends up on row 1645. Hope this helps, Peggy "Dave Peterson" wrote: I wouldn't give up. If you still want to try to fix the problem, then you'll have to share what "doesn't work" means. The only change to that first routine was to unhide the filtered rows. You could do that manually, too. (Data|filter|Showall) piddilin wrote: I copied and replaced your code (below) with everything above Sub Comments_AutoSize() but I'm afraid it still doesn't work but I'm not going to waste any more of your valuable time. I'll live with it - maybe someday I learn code and fix it myself but thanks so much for your time and patience. Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With "Dave Peterson" wrote: I meant before the "for/next" group of statements (or just before the "For Each" line): Option Explicit Sub ResetComments() Dim cmt As Comment If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 Next cmt End Sub piddilin wrote: Still doesn't work, but you said "If you want, you could add this section before the "for next" line:" and I couldn't find the "for next" line so put it before the "Next" like, as follows: Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Next End Sub Sub Comments_AutoSize() 'posted by Dana DeLouis 2000-09-16 Dim MyComments As Comment Dim lArea As Long For Each MyComments In ActiveSheet.Comments With MyComments .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With Next ' comment End Sub "Dave Peterson" wrote: I made a mistake in a different post and the same mistake he If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If should be If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Dave Peterson wrote: I think the simplest solution would be to only run the macro when all the rows are visible. You could even show all the rows in the macro, but then you might be screwing up the filter. If you want, you could add this section before the "for next" line: If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If ==== An alternative may be to just fix the comment position of the cells that are visible??? piddilin wrote: Wow! I created two macros (ResetComments and ResizeComments) and they are awesome! Only problem is ResetComments macro doesn't work if I click the AutoFilter button in a column, select a specific item from the drop down and Right-clicking to Edit Comment creates - I still get the long pointer and see the comment elsewhere on the worksheet. Any fix for that? PREVIOUS DISCUSSION copied he Put them back where they belong with a macro??? From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm gillilal wrote: comments do not move with the cell they were created on and has long pointers to them in the worksheet. They are stationary and not floating with the cell if moved. So the comment may be several pages away from its intended location. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing default font for all comments | Excel Discussion (Misc queries) | |||
Changing default font in comments? | Excel Discussion (Misc queries) | |||
How can I globally change the formatting of all my comments in Exc | Excel Discussion (Misc queries) | |||
How can I pre format all Excel comments to Tahoma, font size 12, . | Excel Discussion (Misc queries) | |||
Comments box markers | Excel Discussion (Misc queries) |