"Comment" format anomaly

[ken]

G'day there One & All,

I have a workbook where my users copy hyperlinks across from one sheet
to another. That works fine, as does the code that then builds a comment
from various cells in the first sheet to give a bit of a description of
what's in the hyperlinked document. That all works fine. What is driving
me nuts, however is that I can't format the comments from within my
calling procedure.

After hours of trial and error failed dismally, a web search found this
little snippet:

Public Sub ChangeCommentFormat()
For Each c In ActiveSheet.Comments
With c.Shape.TextFrame.Characters.Font
.Name = "Tahoma"
.Size = 10
.Bold = False
End With
c.Shape.TextFrame.AutoSize = True
Next
End Sub

It works well, but only if I run it with F5 from within the module.
I've tried calling it from the end of the procedure that builds and adds
my comments. I've called if from the worksheet_change event that calls
the comment building routine. I've tried adapting it to work on an
individual comment called, once again, from within the comment builder.
Nothing works and my comments are then written in bold font with too
small a shape to fit the text in. The above code works quickly and gives
the exact result, but I don't expect my clients to run it manually when
it should just be done each time a comment is inserted from my code.

Does anyone know why it works in this manner? Or does anyone have a
clue as to how to format comments on the fly? To date I've spent over 6
hours trying various methods and am still trying to get it to work.

Hoping someone has more clues than I do.
Thanks for listening
Ken McLennan
Qld Rosewood

 

[Gary''s Student]

Hi Ken:

It must be in you method of calling the formatter. I put the following in a
standard module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/16/2009 by James Ravenswood
'

'
Range("C7").Select
Range("C7").AddComment
Range("C7").Comment.Visible = True
Range("C7").Comment.Text Text:="Hello World"
Range("A1").Select
MsgBox " "
Call ChangeCommentFormat
End Sub
Public Sub ChangeCommentFormat()
For Each c In ActiveSheet.Comments
With c.Shape.TextFrame.Characters.Font
.Name = "Tahoma"
.Size = 10
.Bold = False
End With
c.Shape.TextFrame.AutoSize = True
Next
End Sub

and it worked just fine.

 

[Peter T]

If the routine is in a normal module in the same and in the same workbook as
the change event there should be no problem to call it from the event code.
Put a break in the event on the line that calls the routine.

Regards,
Peter T

 

[ken]

G'day there Peter T,

If the routine is in a normal module in the same and in the same workbook as
the change event there should be no problem to call it from the event code.

That's what I thought too. It's good to know that I haven't completely
lost the plot

Put a break in the event on the line that calls the routine.

I'll have to leave it this morning. I shan't have much time to do
anything fun until later this evening.

Hopefully I'll be able to see what's going on.

Thanks for your assistance,

Ken McLennan
Qld Australia

 

[ken]

G'day there Gary''s Student,

It must be in you method of calling the formatter. I put the following in a
standard module:

Yeah, so it seems. Peter T suggested putting a break in the calling
routine, which I'll have to do later this evening. Won't have a chance
to play until then.

It's got me stuffed as to why it won't work. There's nothing I can see
in the code that would cause it to act like it does, but I must be doing
something wrong.

Thanks for testing it for me,
See ya
Ken

 

[ken]

G'day there Peter T,

If the routine is in a normal module in the same and in the same workbook as
the change event there should be no problem to call it from the event code.
Put a break in the event on the line that calls the routine.

I've done a bit more work with it, and now I'm completely confused.

I still haven't got the routine to work, but I still don't know why.

I've put in error trapping, and there aren't any errors raised. I've
tried calling the formatting routine from various locations within the
comment writing code, with no result.

I have this in the sheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If InRange(Target, Me.Range("B10:B35")) Then
If Not Len(Target.Value) = 0 Then
Call enterOp(Target)
End If
End If
Call ChangeCommentFormat
End If
End Sub

But it doesn't matter where I put the ChangeCommentFormat call within
that code, it doesn't work. I've stepped through the CCF routine, and
it goes through each command but nothing happens. With that in mind, I
tried putting other commands in the "enterOp" routine (it copies a
hyperlink; changes the displayed text; inserts a comment and then sets
the comment text) and found that some of those don't work either. For
instance, the "enterOp" routine works with the selected cell as "rng"
with the hyperlink & comment being successfully inserted in the selected
cell. Having done that, I tried to use .Offset(0,1).select to move the
selection, after adding the comment but that didn't work. Using
Activesheet.Range("A1").select didn't work either. But debug.print and
msgbox "Test" worked as expected.

Stepping through the CCF routine when called from "enterOp" I used the
immediate window to see what I was working with. I found that typing "?
c.text" printed the comment to that window as expected, however "?
c.Shape.TextFrame.Characters.Font.bold" (which should be true or false)
gave the "Object doesn't have this property or method" error.

None of this makes any sense to me. I now have the formatting code
called from the Worksheet_Activate event which sort of works for my
purposes, but I can't understand why it won't work when called
otherwise. Nor why I can't select another cell by the using code.

Does anyone have any ideas?

Thanks for reading,
Ken McLennan
Qld, Australia

 

[Peter T]

See if this works -

' in worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo errExit
Set rng = Intersect(Target, Me.Range("B10:B35")) '<< Change to suit
If Not rng Is Nothing Then
Call CommentStuff(rng)
End If

errExit:
End Sub

' in a normal module
Sub CommentStuff(rng As Range)
Dim cmt As Comment, tf As TextFrame
Dim cell As Range

On Error Resume Next
For Each cell In rng
Set cmt = cell.Comment
On errro GoTo errH
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Shape.TextFrame.Characters.Font.Bold = False
End If

Set tf = cmt.Shape.TextFrame
cnt = tf.Characters.Count

tf.Characters(cnt + 1).Insert _
IIf(cnt, vbLf, "") & Now & " : " & cell.Value
tf.AutoSize = True

Next
Exit Sub
errH:

End Sub

BTW, trust you haven't disabled events in some other code...

Regards,
Peter T

 

[ken]

G'day there Peter,

See if this works -

I gave it a run, but still no change. No errors raised; each command is
highlighted in turn when stepping through, but nothing happens.

It's got me stumped.

Thanks for helping,
Ken McLennan
Qld, Australia

 

[Peter T]

Hello Ken

It's got to be a simple and obvious explanation. Start again in a new
instance, paste the code respectively into the worksheet module and a normal
module.
Put a break in the worksheet code.
change B10
step through the code with F8 into the CommentStuff routine
When done look at the comment in B10
change B10 again

If you don't see a comment look at
Tools, Options, View, Comments and ensue 'None' is not selected

(or equivalent in Excel2007)

Regards,
Peter T

 

[ken]

G'day there Peter T, and Gary's Student,

Thanks for your assistance with this problem. Sorry for not getting
back sooner, but real life & shift work butted in for a week or two.

' in worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo errExit
Set rng = Intersect(Target, Me.Range("B10:B35")) '<< Change to suit
If Not rng Is Nothing Then
Call CommentStuff(rng)
End If

errExit:
End Sub

I never did find out why it didn't work. I've pulled it apart and
reintroduced the routine's line by line, but I always had the same
result. I've got absolutely no idea why it's behaving the way it is.

I've a funny feeling that it's got some connection to differences
between the Range object, and the Worksheet object. The former has a
'.comment' property & the latter has '.comments'. However, as I said,
I've not pinned it down to anything specific.

Fortunately the application will be used in such a way that it doesn't
really matter. There are 2 different clients. Only a select group will
be putting data in, the rest will have 'read only' access from a central
server. When the workbook is opened it automatically opens on the
title page so when someone moves to another worksheet, the comments
will be formatted correctly.

I guess it's kind of a work around, but I'd have preferred to figure
out what's going on.

Thanks again,
See ya
Ken
Qld, Australia

 

[Peter T]

Did you try the example I posted exactly as described. Try it again in a new
session in a new workbook

Ensure the Worksheet_Change is in a worksheet module
Best put the CommentStuff() in a normal module (though for testing could go
in the same sheet module)
Change cells in the range B10:B35, on the same sheet as the event code

Regards,
Peter T

 

[ken]

G'day there Peter T,

Did you try the example I posted exactly as described. Try it again in a new
session in a new workbook

Yeah mate. Tried that when you first posted it. I thought I'd replied,
but having just had a quick look, it's obvious that I haven't. Sorry
about that.

Your code worked exactly the way it was intended. Comments were added
& formatted as required.

I've just tried to copy your techniques with my code i.e. setting the
comment & textframe objects and manipulating those, but with the same
results I've always had - zilch!!

I thought that hurling the 'pooter through a nearby window might have
helped, but my wife wouldn't let me <g>.

Thanks again,
Still trying,
Ken
Qld, Australia.

 

[Peter T]

Ignore the wife and just do it (tip - when she's not there and blame it on
kids). When you finally tumble on whatever stupid thing stopped it working,
and get that sinking doh moment, you can throw something through the other
window!

We've all been there <g>

Regards,
Peter T