| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adjusting Macro | Excel Discussion (Misc queries) | |||
| Comment box size | Excel Discussion (Misc queries) | |||
| Macro / VBA adjusting variable columns | Excel Discussion (Misc queries) | |||
| How do I re-size a comment box using VBA? | Excel Discussion (Misc queries) | |||
| adjusting cell size in spreadsheets | Excel Discussion (Misc queries) | |||
Linear Mode
