Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel VBA and Comment boxes
Hi,
I wrote something that would automatically place the cell content into a comment box, and replace the cell content with the words "See Comments". Now, the user wants the comment box to auto-resize....how come I can't get the comment box selection statement to work? Thanks! Private Sub Worksheet_Change(ByVal Target As Range) Dim strComment As String Dim strRange As String strComment = Target.Value strRange = Target.AddressLocal 'Set Target Row number to avoid this running on changing the header 'Set Target Column to determine column being modified 'Set Target Value so there's no infinite loop from "See Comment" feed If Target.Row 1 And Target.Column = 2 _ 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 = False Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True <- FAIL RIGHT HERE With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With End If End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel VBA and Comment boxes
Change
Range(strRange).Comment.Visible = False to Range(strRange).Comment.Visible = True Following line will fail (as it is doing now) if comment is not visible... Range(strRange).Comment.Shape.Select True -- Always provide your feedback... "DCPan" wrote: Hi, I wrote something that would automatically place the cell content into a comment box, and replace the cell content with the words "See Comments". Now, the user wants the comment box to auto-resize....how come I can't get the comment box selection statement to work? Thanks! Private Sub Worksheet_Change(ByVal Target As Range) Dim strComment As String Dim strRange As String strComment = Target.Value strRange = Target.AddressLocal 'Set Target Row number to avoid this running on changing the header 'Set Target Column to determine column being modified 'Set Target Value so there's no infinite loop from "See Comment" feed If Target.Row 1 And Target.Column = 2 _ 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 = False Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True <- FAIL RIGHT HERE With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With End If End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel VBA and Comment boxes
That did the trick! You rock! (I rated the post too!)
"Sheeloo" wrote: Change Range(strRange).Comment.Visible = False to Range(strRange).Comment.Visible = True Following line will fail (as it is doing now) if comment is not visible... Range(strRange).Comment.Shape.Select True -- Always provide your feedback... "DCPan" wrote: Hi, I wrote something that would automatically place the cell content into a comment box, and replace the cell content with the words "See Comments". Now, the user wants the comment box to auto-resize....how come I can't get the comment box selection statement to work? Thanks! Private Sub Worksheet_Change(ByVal Target As Range) Dim strComment As String Dim strRange As String strComment = Target.Value strRange = Target.AddressLocal 'Set Target Row number to avoid this running on changing the header 'Set Target Column to determine column being modified 'Set Target Value so there's no infinite loop from "See Comment" feed If Target.Row 1 And Target.Column = 2 _ 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 = False Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True <- FAIL RIGHT HERE With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With End If End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel VBA and Comment boxes
Thanks for the feeback... it helps everyone.
See http://www.contextures.com/xlcomments03.html for excellent examples for dealing with comments... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "DCPan" wrote: That did the trick! You rock! (I rated the post too!) "Sheeloo" wrote: Change Range(strRange).Comment.Visible = False to Range(strRange).Comment.Visible = True Following line will fail (as it is doing now) if comment is not visible... Range(strRange).Comment.Shape.Select True -- Always provide your feedback... "DCPan" wrote: Hi, I wrote something that would automatically place the cell content into a comment box, and replace the cell content with the words "See Comments". Now, the user wants the comment box to auto-resize....how come I can't get the comment box selection statement to work? Thanks! Private Sub Worksheet_Change(ByVal Target As Range) Dim strComment As String Dim strRange As String strComment = Target.Value strRange = Target.AddressLocal 'Set Target Row number to avoid this running on changing the header 'Set Target Column to determine column being modified 'Set Target Value so there's no infinite loop from "See Comment" feed If Target.Row 1 And Target.Column = 2 _ 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 = False Range(strRange).Comment.Text Text:=strComment Range(strRange).Comment.Shape.Select True <- FAIL RIGHT HERE With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel spreadsheet comment boxes keep shrinking smaller than text. | Excel Worksheet Functions | |||
Excel Comment boxes | Excel Discussion (Misc queries) | |||
stablize the Excel comment boxes (size and location) | Excel Discussion (Misc queries) | |||
In excel spreadsheet my comment boxes change sizes randomly why? | Excel Worksheet Functions | |||
How do I change the default font for comment boxes in Excel | Excel Discussion (Misc queries) |