Need to display comment data in new text box

2

2mutch

New Member
Joined
Oct 11, 2009
Messages
15
****** name="Title" content=""> ****** name="Keywords" content=""> ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Word.Document"> ****** name="Generator" content="Microsoft Word 11"> ****** name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file://localhost/Users/sinclair/Library/Caches/TemporaryItems/msoclip1/01/clip_filelist.xml"> <!--[if gte mso 9]><xml> <o:DocumentProperties> <o:Template>Normal</o:Template> <o:Revision>0</o:Revision> <o:TotalTime>0</o:TotalTime> <o:Pages>1</o:Pages> <o:Words>70</o:Words> <o:Characters>400</o:Characters> <o:Company>BesTel Solutions, Inc.</o:Company> <o:Lines>3</o:Lines> <o:Paragraphs>1</o:Paragraphs> <o:CharactersWithSpaces>491</o:CharactersWithSpaces> <o:Version>11.1282</o:Version> </o:DocumentProperties> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:Zoom>0</w:Zoom> <w:DoNotShowRevisions/> <w:DoNotPrintRevisions/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>0</w:DisplayVerticalDrawingGridEvery> <w:UseMarginsForDrawingGridOrigin/> </w:WordDocument> </xml><![endif]--> <style> <!-- /* Font Definitions */ @font-face {font-family:"Times New Roman"; panose-1:0 2 2 6 3 5 4 5 2 3; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman";} table.MsoNormalTable {mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> <!--StartFragment--> I have two sheets in a workbook. The data sheet contains all the data and the other sheet just displays the selected data, using formulas to lookup the data. One of the cells on the data sheet is a comment field. I have a formula to show the data in display sheet, but it is too long and you can’t see it all. If you click in the cell, it just displays the formula in the formula bar. Is there a way to display the referenced data from the comment cell in a large text box on the display sheet, just by clicking in the cell?
<!--EndFragment-->
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I guess you need a VBA .... UDF

Code: 
Function Clookup(lookup_value As Variant, table_array As Range, col_index_num As Long) As Variant
    matchrow = Application.Match(lookup_value, table_array.Columns(1), 0)
    If IsError(matchrow) Then
        Clookup = "Not Found" 'or blank .....
    Else
        Clookup = table_array.Columns(col_index_num).Cells(1)(matchrow).Comment.Text
    End If
End Function
 
Upvote 0
****** name="Title" content=""> ****** name="Keywords" content=""> ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Word.Document"> ****** name="Generator" content="Microsoft Word 11"> ****** name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file://localhost/Users/sinclair/Library/Caches/TemporaryItems/msoclip1/01/clip_filelist.xml"> <!--[if gte mso 9]><xml> <o:DocumentProperties> <o:Template>Normal</o:Template> <o:Revision>0</o:Revision> <o:TotalTime>0</o:TotalTime> <o:Pages>1</o:Pages> <o:Words>87</o:Words> <o:Characters>501</o:Characters> <o:Company>BesTel Solutions, Inc.</o:Company> <o:Lines>4</o:Lines> <o:Paragraphs>1</o:Paragraphs> <o:CharactersWithSpaces>615</o:CharactersWithSpaces> <o:Version>11.1282</o:Version> </o:DocumentProperties> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:Zoom>0</w:Zoom> <w:DoNotShowRevisions/> <w:DoNotPrintRevisions/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>0</w:DisplayVerticalDrawingGridEvery> <w:UseMarginsForDrawingGridOrigin/> </w:WordDocument> </xml><![endif]--> <style> <!-- /* Font Definitions */ @font-face {font-family:"Times New Roman"; panose-1:0 2 2 6 3 5 4 5 2 3; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} @font-face {font-family:Verdana; panose-1:0 2 11 6 4 3 5 4 4 2; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman";} table.MsoNormalTable {mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> <!--StartFragment--> This is the function I am using to get the data, it displays the comment from the data sheet in the cell on the display sheet correctly, but is to long to read it in the cell.
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
Function getComment(incell) As String
' accepts a cell as input and returns its comments (if any) back as a string
On Error Resume Next
getComment = incell.Comment.Text
End Function
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
This is the formula
=IF(N($A20),getComment(INDEX(Data!B$2:B$101,$A20)),"-")<o:p></o:p>
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
How do I get it to display in a text box on the screen when they click on the cell (don’t want it displayed all the time, just when they click on it), and close the box <!--EndFragment-->
 
Upvote 0
Not sure if this would be the best Sol ... but,

Display comment in a userform (Use ShowModal Property = False, so that the user form does not block ur sheet)

Display this forn on the Selection Changes event
(Any cell in Column B will trigger this macro)
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then
    Exit Sub
End If
UserForm1.TextBox1.Text = Target.Value
UserForm1.TextBox1.WordWrap = True
UserForm1.TextBox1.MultiLine = True

If UserForm1.Visible = True Then
    DoEvents
Else
    UserForm1.Show
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
M
  • Question
index match and dynamic subtraction
Replies
2
Views
887
member23
M
P
  • Question
vba to run through sorted list and copy rows in each sorted group into a new tab
Replies
3
Views
984
vcoolio
V
D
  • Question
Changes in text box height as new text is added or removed
Replies
8
Views
1K
dpaton05
D
S
  • Question
Macro for DATEVALUE
Replies
3
Views
1K
jasonb75
J

Forum statistics

Threads
1,221,314
Messages
6,159,187
Members
451,544
Latest member
MrsGrayMarlin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top