|
Adjusting comment box size by Macro
Hi all
How to adjust comment box size and the font of the comment by macro? The following is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim wsCmt As Worksheet Dim strCmt As String Dim rngCmt As Range Dim ar As Integer Set wsCmt = Worksheets("rcpt") Set rngCmt = wsCmt.Range("ndata") ar = ActiveCell.Row ' Clear Comments On Error Resume Next Selection.SpecialCells(xlCellTypeComments).ClearCo mments ' Extracting the comment value On Error GoTo endit Select Case Val(ActiveCell.Column) Case 2: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 2, 0) GoTo feedisplay Case 9: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 8, 0) GoTo feedisplay Case 10: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 9, 0) GoTo feedisplay Case 11: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 10, 0) GoTo feedisplay Case 12: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 11, 0) GoTo feedisplay Case 13: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 12, 0) GoTo feedisplay Case 14: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 13, 0) GoTo feedisplay Case 15: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 14, 0) GoTo feedisplay Case Else: Exit Sub End Select ' Display fee balances in comments feedisplay: ActiveCell.AddComment CStr(strCmt) ActiveCell.Comment.Visible = True Exit Sub endit: End Sub Thanks in advance. With regards Sreedhar |
Adjusting comment box size by Macro
Hi
take a look at code written by Debra Dalgleish and Dave Peterson at http://www.contextures.com/xlcomments03.html#Resize -- Regards Roger Govier "yshridhar" wrote in message ... Hi all How to adjust comment box size and the font of the comment by macro? The following is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim wsCmt As Worksheet Dim strCmt As String Dim rngCmt As Range Dim ar As Integer Set wsCmt = Worksheets("rcpt") Set rngCmt = wsCmt.Range("ndata") ar = ActiveCell.Row ' Clear Comments On Error Resume Next Selection.SpecialCells(xlCellTypeComments).ClearCo mments ' Extracting the comment value On Error GoTo endit Select Case Val(ActiveCell.Column) Case 2: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 2, 0) GoTo feedisplay Case 9: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 8, 0) GoTo feedisplay Case 10: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 9, 0) GoTo feedisplay Case 11: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 10, 0) GoTo feedisplay Case 12: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 11, 0) GoTo feedisplay Case 13: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 12, 0) GoTo feedisplay Case 14: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 13, 0) GoTo feedisplay Case 15: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 14, 0) GoTo feedisplay Case Else: Exit Sub End Select ' Display fee balances in comments feedisplay: ActiveCell.AddComment CStr(strCmt) ActiveCell.Comment.Visible = True Exit Sub endit: End Sub Thanks in advance. With regards Sreedhar |
Adjusting comment box size by Macro
Thanks Roger. The article is very useful for me.
With regards Sreedhar "Roger Govier" wrote: Hi take a look at code written by Debra Dalgleish and Dave Peterson at http://www.contextures.com/xlcomments03.html#Resize -- Regards Roger Govier "yshridhar" wrote in message ... Hi all How to adjust comment box size and the font of the comment by macro? The following is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim wsCmt As Worksheet Dim strCmt As String Dim rngCmt As Range Dim ar As Integer Set wsCmt = Worksheets("rcpt") Set rngCmt = wsCmt.Range("ndata") ar = ActiveCell.Row ' Clear Comments On Error Resume Next Selection.SpecialCells(xlCellTypeComments).ClearCo mments ' Extracting the comment value On Error GoTo endit Select Case Val(ActiveCell.Column) Case 2: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 2, 0) GoTo feedisplay Case 9: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 8, 0) GoTo feedisplay Case 10: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 9, 0) GoTo feedisplay Case 11: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 10, 0) GoTo feedisplay Case 12: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 11, 0) GoTo feedisplay Case 13: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 12, 0) GoTo feedisplay Case 14: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 13, 0) GoTo feedisplay Case 15: strCmt = Application.WorksheetFunction.VLookup(Range("b" & ar), rngCmt, 14, 0) GoTo feedisplay Case Else: Exit Sub End Select ' Display fee balances in comments feedisplay: ActiveCell.AddComment CStr(strCmt) ActiveCell.Comment.Visible = True Exit Sub endit: End Sub Thanks in advance. With regards Sreedhar |
| All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com