+ Reply to Thread
Results 1 to 5 of 5

Scrolling within a comments box

Hybrid View

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    Swindon England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Scrolling within a comments box

    Hi folks - Does anyone know how to get scroll bars operating within a comments box?

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Scrolling within a comments box

    Excel does not support scroll bars in a comment box.
    You could, though, create a userform to display the comments in a textbox which can have a scroll bar.

    See attached. Select cell A1 on sheet1.
    Attached Files Attached Files
    Last edited by Palmetto; 09-21-2009 at 07:44 AM. Reason: added workbook with VBA userform example

  3. #3
    Registered User
    Join Date
    09-21-2009
    Location
    Swindon England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Scrolling within a comments box

    Thanks for this it's excellent - question is how do I achieve it?

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Scrolling within a comments box

    Thanks for this it's excellent - question is how do I achieve it?
    Take some time to explore the workbook, particularly the VBA.
    To access the VB Editor, press Alt + F11 keys. When finished with the VBE, press Alt + Q to exit.

    In the workbook is a very basic userform with a single textbox that uses this simple code to get the comments from the activecell:

    Option Explicit
    
    Private Sub TextBox1_Enter()
        With Me.TextBox1
            .Text = ActiveCell.Comment.Text
            .WordWrap = True
            .ScrollBars = fmScrollBarsVertical
            .MultiLine = True
        End With
    End Sub
    
    Private Sub UserForm_Activate()
        Me.Top = ActiveCell.Offset(8, 0).Top
        Me.Left = ActiveCell.Offset(0, 2).Left
    End Sub
    The user form is currently on set to show only when you select cell A1, but can be amended to encompass a larger range of cells. The worksheet module uses this bit of code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        If Not Intersect(Target, Range("A1")) Is Nothing Then
        UserForm1.Show
        End If
    
    End Sub
    The code is not fully developed or very robust and was only intended to show you a workaround since comment boxes don't support scrollbars.

  5. #5
    Registered User
    Join Date
    09-21-2009
    Location
    Swindon England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Thumbs up Re: Scrolling within a comments box

    First rate, thank you very much.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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