+ Reply to Thread
Results 1 to 5 of 5

Get string in Comment Box to show as cell value

Hybrid View

  1. #1
    Registered User
    Join Date
    10-30-2017
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    30

    Get string in Comment Box to show as cell value

    Hi,
    Below code works fine except for the format in comment boxes.
    Each cell in rng, row 4, is a number formatted with single decimal place (eg. 2,7).
    Each cell in rng, row 5, is a number formatted with no decimal place (eg. 1).
    Each cell in rng, row 6, is a number formatted with percentage format(eg. 67%).
    All cells in rng, row 4-6, have formulas.
    Now Comment Box show: 2,66666666666667 - 1 - 0,666666666666667
    I want Comment Box to show: 2,7 - 1 - 67%
    Someone know how to do that?

    Sub CommRank()
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Arr     As Variant
    Dim rng     As Range
    Dim cell    As Range
    Dim i
    Dim Name    As Variant
    Dim DEF     As Variant
    Dim ABC     As Variant
    Dim target  As Range
    
    Set wb1 = Workbooks("WorkbookName")
    Set wb2 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    Set ws2 = wb2.Sheets("Sheet1")
    
    Application.ScreenUpdating = False
    ws2.Range("B1").Activate
    Set rng = ws1.Range("B1:AT6")                   'Range to find values for Comments
    
    With rng
        Arr = rng
            For Each cell In ws2.Range("AC13:AC20") 'Range for Comments
                Name = ActiveCell
                DEF = ActiveCell.Offset(1, 1)
                ABC = ActiveCell.Offset(, 1)
                    For i = LBound(Arr, 2) To UBound(Arr, 2)
                        If Arr(1, i) = Name And Arr(2, i) = DEF And Arr(3, i) = ABC Then
                            Set target = cell
                            target.ClearComments
                            target.AddComment
                            With target.Comment
                                .Text Arr(4, i) & " - " & Arr(5, i) & " - " & Arr(6, i)
                                .Shape.Shadow.Visible = msoFalse
                                .Visible = False
                                .Shape.TextFrame.AutoSize = True
                            End With
                    Exit For
                        End If
                    Next i
                ActiveCell.Offset(, 2).Select
            Next cell
        ws2.Range("A1").Select
    Application.ScreenUpdating = True
    End With
    End Sub
    Any help will be much appreciated.
    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Get string in Comment Box to show as cell value

    Try
    Change this
    .Text Arr(4, i) & " - " & Arr(5, i) & " - " & Arr(6, i)
    to
    .Text Format(arr(4, i), "#,#") & " - " & arr(5, i) & " - " & Format(arr(6, i), "#,##%")
    Kind regards
    Leo

  3. #3
    Registered User
    Join Date
    10-30-2017
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Get string in Comment Box to show as cell value

    Hi LeoTaxi,
    Thanks for quick reply.
    Almost there I think.
    Now Comment Box show: 3 - 1 - 67%
    I tried adding extra # but that didn't work.
    Any idea how to fix it?
    Thanks in advance!

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Get string in Comment Box to show as cell value

    Sorry my mistake, should be

    .Text Format(Arr(4, i), "#,##0.0") & " - " & Arr(5, i) & " - " & Format(Arr(6, i), "#,##%")

    Kind regards
    Leo

  5. #5
    Registered User
    Join Date
    10-30-2017
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Get string in Comment Box to show as cell value

    Excelent!
    I have tried for two days now so I am very thankful for your help.
    Thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] show/hide comment does not appear when right clicking on cell
    By flupsie in forum Excel General
    Replies: 3
    Last Post: 01-21-2018, 07:36 AM
  2. [SOLVED] Function to return comment from a cell, or null string if no comment
    By Monkihunta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2017, 01:01 PM
  3. [SOLVED] Show comment on mouse over cell rather than over comment icon....?
    By BellyGas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2015, 09:05 AM
  4. Disable Delete Comment, Edit Comment, Show/Hide Comment
    By Shanthan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2013, 06:12 AM
  5. [SOLVED] Can't show cell comment text in Msgbox
    By zbor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-02-2012, 05:51 AM
  6. Cell Comment Truncating String?
    By bstubbs in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2009, 03:02 PM
  7. Replies: 2
    Last Post: 11-09-2005, 02:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1