Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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. -- Gary''s Student - gsnu200903 "ken" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Comment" format anomaly
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 "ken" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert cell "contents" into a "comment" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date? | Excel Programming | |||
can you change the default format for a "comment" in excel? | Excel Discussion (Misc queries) | |||
Scroll Bar missing "Control" tab in "Format Properties" dialog box | Excel Discussion (Misc queries) |