Comments

P

piddilin

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 here:
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
 
D

Dave Peterson

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???
 
D

Dave Peterson

I made a mistake in a different post and the same mistake here:

If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If

should be

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
 
P

piddilin

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 said:
I made a mistake in a different post and the same mistake here:

If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If

should be

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Click to expand...
 
D

Dave Peterson

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


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
Click to expand...
 
P

piddilin

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 said:
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
Click to expand...
 
D

Dave Peterson

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)
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
Click to expand...
 
P

piddilin

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
 
D

Dave Peterson

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.



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
Click to expand...
 
Top