Styjun

Why force the nose of 737 Max down in the first place?

What is more environmentally friendly? An A320 or a car?

Exploiting the delay when a festival ticket is scanned

Is SecureRandom.ints() secure?

Who said "one can be a powerful king with a very small sceptre"?

Why is my fluorescent tube orange on one side, white on the other and dark in the middle?

Can Papyrus be folded?

Why does the Eurostar not show youth pricing?

Problem with Eigenvectors

How should I quote American English speakers in a British English essay?

How did the SysRq key get onto modern keyboards if it's rarely used?

Is there an antonym(a complementary antonym) for "spicy" or "hot" regarding food (I DO NOT mean "seasoned" but "hot")?

Does dual boot harm a laptop battery or reduce its life?

What is the general way to say something like "in your room?" without a verb?

How to season a character?

Why does aggregate initialization not work anymore since C++20 if a constructor is explicitly defaulted or deleted?

Omnidirectional LED, is it possible?

Self-deportation of American Citizens from US

Why did House of Representatives need to condemn Trumps Tweets?

Assuring luggage isn't lost with short layover

Is there any app for reduce battery draining for ios 12?

How to improve king safety

Do 3/8 (37.5%) of Quadratics Have No x-Intercepts?

How did "ordnance" lose the "i"?

How to auto-size comment box to fit its content?

excel vba get range of user selected range by mouse and copy it to clipboardprogrammatically excel cells to be auto fit width & heightExcel VBA - read cell value from codehow to prevent re sizing of buttons after release of auto filter in excel 2010 using vbaHow to fit range of calculate calomn to pivot table size in Excel 2013Copy only selected table columns after filter to new worksheetChange comment size relative to original size of fill picture in Excel vbaHow to auto-size Excel comment?Excel VBA macro to send emails to unique users in range

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

1

I am trying to turn the text contents of a single cell into a comment on that cell and then have the comment auto size to fit the true size of the contents. I found two separate macros that work but I am having trouble combining them into one macro.

The below code works by converting the selected cell into a comment.
https://sumtips.com/tips-n-tricks/convert-excel-cell-contents-comments-vba/

Sub ConvertToComment()
Dim C As Range
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

The below code works in auto sizing all the comments on the sheet.
https://quadexcel.com/how-to-auto-size-comment-box-to-fit-its-content-in-excel/

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

How can I incorporate the below line into the ConvertToComment() macro at the top?

xComment.Shape.TextFrame.AutoSize = True

The below code is my sad attempt to add a line I think is important to make it work the way I want it to. Create the comment and Autosize the created comment to fit the content. I am still very new to VBA.

Sub ConvertToComment()
Dim C As Range
Dim xComment As COMMENT
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 --> COMMENT.Text C.Shape.TextFrame.AutoSize = True
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

Error Message "Object doesn't support this project or method"

excel vba

share|improve this question

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There are two statements on the same line. Try just C.Shape.TextFrame.AutoSize = True
  
  – PeterT
  Mar 26 at 21:06
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thank you for the response. I have just tried this line in the place of my previous incorrect line. It gave error 438. "Object doesn't support this property or method". So I added C.COMMENT.Shape.TextFrame.AutoSize = True and it worked. Thank you so much.
  
  – Steven Gilbert
  Mar 27 at 12:49
  
  

  
  
  
  

add a comment | 

0

1

I am trying to turn the text contents of a single cell into a comment on that cell and then have the comment auto size to fit the true size of the contents. I found two separate macros that work but I am having trouble combining them into one macro.

The below code works by converting the selected cell into a comment.
https://sumtips.com/tips-n-tricks/convert-excel-cell-contents-comments-vba/

Sub ConvertToComment()
Dim C As Range
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

The below code works in auto sizing all the comments on the sheet.
https://quadexcel.com/how-to-auto-size-comment-box-to-fit-its-content-in-excel/

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

How can I incorporate the below line into the ConvertToComment() macro at the top?

xComment.Shape.TextFrame.AutoSize = True

The below code is my sad attempt to add a line I think is important to make it work the way I want it to. Create the comment and Autosize the created comment to fit the content. I am still very new to VBA.

Sub ConvertToComment()
Dim C As Range
Dim xComment As COMMENT
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 --> COMMENT.Text C.Shape.TextFrame.AutoSize = True
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

Error Message "Object doesn't support this project or method"

excel vba

share|improve this question

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There are two statements on the same line. Try just C.Shape.TextFrame.AutoSize = True
  
  – PeterT
  Mar 26 at 21:06
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thank you for the response. I have just tried this line in the place of my previous incorrect line. It gave error 438. "Object doesn't support this property or method". So I added C.COMMENT.Shape.TextFrame.AutoSize = True and it worked. Thank you so much.
  
  – Steven Gilbert
  Mar 27 at 12:49
  
  

  
  
  
  

add a comment | 

I am trying to turn the text contents of a single cell into a comment on that cell and then have the comment auto size to fit the true size of the contents. I found two separate macros that work but I am having trouble combining them into one macro.

The below code works by converting the selected cell into a comment.
https://sumtips.com/tips-n-tricks/convert-excel-cell-contents-comments-vba/

Sub ConvertToComment()
Dim C As Range
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

The below code works in auto sizing all the comments on the sheet.
https://quadexcel.com/how-to-auto-size-comment-box-to-fit-its-content-in-excel/

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

How can I incorporate the below line into the ConvertToComment() macro at the top?

xComment.Shape.TextFrame.AutoSize = True

The below code is my sad attempt to add a line I think is important to make it work the way I want it to. Create the comment and Autosize the created comment to fit the content. I am still very new to VBA.

Sub ConvertToComment()
Dim C As Range
Dim xComment As COMMENT
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 --> COMMENT.Text C.Shape.TextFrame.AutoSize = True
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

Error Message "Object doesn't support this project or method"

excel vba

share|improve this question

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

Sub ConvertToComment()
Dim C As Range
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

xComment.Shape.TextFrame.AutoSize = True

Sub ConvertToComment()
Dim C As Range
Dim xComment As COMMENT
For Each C In Selection
 C.ClearComments
 If Len(C.Value) > 0 Then
 C.AddComment
 C.COMMENT.Text C.Value & ""
 --> COMMENT.Text C.Shape.TextFrame.AutoSize = True
 End If
 'Optional: Delete cell content after converting
 C = "See Comment"
 Next C
End Sub

share|improve this question

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

share|improve this question

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

edited Mar 26 at 20:34

Ahmed Abdelhameed

7,61588 gold badges2525 silver badges5151 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges

asked Mar 26 at 20:31

Steven GilbertSteven Gilbert

1211 silver badge66 bronze badges