| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA "auto-sizing" comment box
Hi all,
The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
Suppose somehow you were able to autosize the width...
how would you expect to see a comment like "Test Comment"? Comment box expanded vertically with one letter per line? If yes then use ..Orientation = xlVertical If you want horizontal orientation and get the comments adjusted vertically then while typing the comment you need to do ALT-ENTER for second line or insert that through code where you want the comment to expand vertically... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "DCPan" wrote: Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
So, for example, if I have something like "Knight Rider 2008 show isn't going
to make it past Season 1", I hope it shows up like" Knight Rider 2008 isn't going to make it past Season 1 So, is there any way I could specify something like width 16, autoheight? Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
Where is this code located... in an event procedure like Worksheet_Change?
What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
The code is located in the worksheet for worksheet_change.
For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
There is no method provided by VBA but you can do something like this;
With mc 'mc having reference to the comment .Shape.TextFrame.AutoSize = True shapeArea = .Shape.Width * .Shape.Height .Shape.Width = 50 .Shape.Height = shapeArea / .Shape.Width End With -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "DCPan" wrote: So, for example, if I have something like "Knight Rider 2008 show isn't going to make it past Season 1", I hope it shows up like" Knight Rider 2008 isn't going to make it past Season 1 So, is there any way I could specify something like width 16, autoheight? Thanks! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
That worked beautifully! Thanks again!
"Sheeloo" wrote: There is no method provided by VBA but you can do something like this; With mc 'mc having reference to the comment .Shape.TextFrame.AutoSize = True shapeArea = .Shape.Width * .Shape.Height .Shape.Width = 50 .Shape.Height = shapeArea / .Shape.Width End With -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "DCPan" wrote: So, for example, if I have something like "Knight Rider 2008 show isn't going to make it past Season 1", I hope it shows up like" Knight Rider 2008 isn't going to make it past Season 1 So, is there any way I could specify something like width 16, autoheight? Thanks! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
How about something like this...
Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
Thank you for teaching me structure!
You rock! "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
I forgot to put that safety to prevent the code from crashing as well when
multiple targets are selected. I guess you once you hit the exit sub, it doesn't matter, but I thought you are one "End If" short? Thanks again! "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
What does the application.enableevents do? Does it keep it from going into
an infinite loop when you reset the cell value with "see comment"? "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
YES, you got it.
It prevents Worksheet_Change event from firing again in this. In general it prevents all applicable application events from firing. Thanks for your feedback... -- "DCPan" wrote: What does the application.enableevents do? Does it keep it from going into an infinite loop when you reset the cell value with "see comment"? "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
There are two types of If..Then statements... a single line method
(statement to be executed is located on the same line as the If...Then statement) and a block structured method (multiple lines of code to be executed for the give logical test). Only the block method requires the End If statement (it tells the If..Then statement where the last line controlled by its logical test is located at); the single line method, by its very nature, does not need to signal where the last line is because there is only one statement and it is located on the same line immediately following the Then statement. -- Rick (MVP - Excel) "DCPan" wrote in message ... I forgot to put that safety to prevent the code from crashing as well when multiple targets are selected. I guess you once you hit the exit sub, it doesn't matter, but I thought you are one "End If" short? Thanks again! "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
Not an infinite loop (at least not in this case, but it is definitely
possible to construct code that will loop indefinitely), rather it stops an unnecessary loop from occurring. When this line is executed... ..Value = "See Comment" the assignment causes a Change event to fire. In the case of your code, nothing will happen because the entry ("See Comment") is not one that will cause your code to take any action, but there is no reason to let the action cause the extra event firing... setting EnableEvents to False makes VB not execute ANY other events until it is set to True again. Your code is simple and turning it off and then on again around the assignment is easy to do here; however, there are lots of situation where that is not the case. For example, in those cases where an error could occur before you turn EnableEvents back on, it is imperative to use an error handler. For example, in pseudo-code... Private Sub Worksheet_Change(ByVal Target As Range) If <<some test Then On Error GoTo CleanUpAfterError Application.EnableEvents = False Target.Value = <<some value ' ' More code here which could possibly generate an error ' End If Exit Sub CleanUpAfterError: Application.EnableEvents = True ' ' Other clean up code, if any, goes here ' End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... What does the application.enableevents do? Does it keep it from going into an infinite loop when you reset the cell value with "see comment"? "Rick Rothstein" wrote: How about something like this... Private Sub Worksheet_Change(ByVal Target As Range) With Target If Target.Count < 1 Then Exit Sub If .Value < "See Comment" Then If .Row 1 And .Column = 14 Then .ClearComments If .Value < "" Then .AddComment .Value .Comment.Shape.Width = 95 .Application.EnableEvents = False .Value = "See Comment" .Application.EnableEvents = True End If End If End If End With End Sub -- Rick (MVP - Excel) "DCPan" wrote in message ... The code is located in the worksheet for worksheet_change. For some strange reason, when I use the target in my code, it fails, that's why I handed Target.AddressLocal off to strRange. Thanks! "Rick Rothstein" wrote: Where is this code located... in an event procedure like Worksheet_Change? What is strRange and, if it is what I think it is, why are you using Range(strRange) instead of Target in your code? -- Rick (MVP - Excel) "DCPan" wrote in message ... Hi all, The script I attached below will place what is typed in the cell into a comment box, then replace the cell with "see comments" and delete the comment box when "see comments" is erased. Now, the question is...I have auto-size turned on...but why does the comment box only format itself "length wise"? Can I specify a comment box width and only have it autosize vertically? Thanks! _________________ If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value < "See Comment" _ And Range(strRange).Value < "" Then Range(strRange).Value = "See Comment" Range(strRange).Select Range(strRange).AddComment Range(strRange).Comment.Visible = True Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With Range(strRange).Comment.Visible = False Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select Else If Target.Row 1 And Target.Column = 14 _ And Range(strRange).Value = "" Then Range(strRange).Select Range(strRange).ClearComments End If Range(strRange).Select 'Range("A" & (Target.Row + 1)).Select End If |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VBA "auto-sizing" comment box
I was wondering how error handling in Excel VBA worked...I'm just fumbling my
way through as I'm slightly more familiar with Access VBA. Thanks again for taking the time to help me out! David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert cell "contents" into a "comment" | Excel Discussion (Misc queries) | |||
| text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
| Run macro only if Field E10 = "WO ID" and AB10 = "Spec Sizing" | Excel Discussion (Misc queries) | |||
| Run macro only if Field E10 = "WO ID" and AB10 = "Spec Sizing" | Excel Discussion (Misc queries) | |||
| Comment Box Auto-sizing | Excel Worksheet Functions | |||
Linear Mode
