Results 1 to 9 of 9

Thread: comment box/ Form for entering comments and it opens as a pop up

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default comment box/ Form for entering comments and it opens as a pop up

    Hello,

    I am trying to create a worksheet with a timetable and some time slot needs some action and the detail of the actions needed to be entered. Can i create a form that takes input and store it as a comment and when the cell is selected the pop-up/window can displays the comment in a enlarged state.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,863
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    1. Is your timetable regular (like a school timetable) with the same timeslots every day as in sheet below
    - if not please provide more detail

    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]A[/COLOR]
    [COLOR=#FFFFFF ]B[/COLOR]
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]F[/COLOR]
    [COLOR=#FFFFFF ]1[/COLOR]
    Mon Tue Wed Thu Fri
    [COLOR=#FFFFFF ]2[/COLOR]
    9 - 9:30
    [COLOR=#FFFFFF ]3[/COLOR]
    9:30 - 10:15
    [COLOR=#FFFFFF ]4[/COLOR]
    10:15 -10:30
    [COLOR=#FFFFFF ]5[/COLOR]
    10:30 - 11:15
    [COLOR=#FFFFFF ]6[/COLOR]
    11:15 - 12 noon
    [COLOR=#FFFFFF ]7[/COLOR]
    noon - 1.30
    [COLOR=#FFFFFF ]8[/COLOR]
    1.30 - 2:15
    [COLOR=#FFFFFF ]9[/COLOR]
    2:15 - 2.30
    [COLOR=#FFFFFF ]10[/COLOR]
    3:30 - 3:15
    [COLOR=#FFFFFF ]11[/COLOR]
    3:15 - 4
    Sheet: Sheet2

    2. When is the userform displayed (click on button ? right-click on a timeslot ? )

    3. Other than inserting a comment in the cell what else does the userform do ?

    4. Which fields should the userform contain ?

    5. Please confirm that you are referring to what was formerly called "comment" but has been annoyingly renamed "note" by microsoft
    see https://support.office.com/en-gb/art...8-7669077b7be3
    Last edited by Yongle; Jul 22nd, 2019 at 06:23 AM.

  3. #3
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    Thank you for the quick reply. Please find the answers below


    1. Yes pretty much the same (will give the screen shot for the clear idea). Trying to use the user form to enter a comment in a particular cell rather than filling up multiple cells with a form.


    2. The user form can be displayed by selecting as an option through right click to the cell (eg :Wednesday 9-9:30 ) and the standard template would appear for the user to fill. (Any other suggestion on activating it is more than welcomed )


    3. The sole purpose of the operation is to have a better view of the comment with the template, which is not available with the default method. So once the comment is entered and the background color is set (say : orange) and the user can know there is comment in the cell and by double clicking on the cell, a pop-up or a window would appear to display the comment.


    4. The user form would be a template with fields like :


    a. Comments
    b. Time:
    c. place:
    d. Result:
    e. Line number:
    etc


    5. Yes pretty much "Notes".

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,805
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    I'm a little confused.

    Your object is to add a comment to a cell.
    And you want to use a UserForm to do this.
    And you will have several TextBoxes on the UserForm and then those values will be entered into the Comment?

    And then when you double click on the cell you want the comment Text to be displayed in a larger window.

    Would putting the Comment into a Message Box that would popup work?


    You said this:
    The sole purpose of the operation is to have a better view of the comment with the template

    What is the Template?

    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,863
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    Perhaps something like this

    In STANDARD module (to make variable available to ALL modules)
    - place at TOP of module above ALL procedures
    Code:
    Public CommentCell As Range
    In SHEET module
    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
        ' amend range to the one that matches your timetable
        If Not Intersect(Target, [B2:H8]) Is Nothing Then
            Cancel = True
            Set CommentCell = Target.Cells(1, 1)
            UserForm1.Show
        End If
    End Sub
    In USERFORM module

    Code:
    Private Sub CommandButton1_Click()
        Dim txt As String
        Const N = vbCrLf
    
    'create string for comment (amend to suit your own requirements)
        txt = tb_Comments & N & tb_Time & N & tb_Place & N & tb_Result & N & tb_LineNumber
        
    'insert comment in cell   
        With CommentCell
            On Error Resume Next
            .Interior.Color = 13158600    '= grey
            .Comment.Delete
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:=txt
        End With
        Set CommentCell = Nothing
        Unload Me
    End Sub
    Userform textboxes ..
    tb_Comments
    tb_Time
    tb_Place
    tb_Result
    tb_LineNumber

  6. #6
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    Thank You Yongle for the reply.


    I used the code which you sent (created a module and put all the procedure as guided). But not able to assign it to the macros or trigger the procedure. Can you please suggest me how to trigger the procedures which you sent.

    Thanking you!

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,863
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    I should have added that you need to create UserForm1 containing 5 textboxes using the names I listed, plus a command button

    Right-click on cell in nominated range should trigger the userform to open
    When userform opens, place values in the 5 text boxes and click on command button to insert cell comment

    Let me know how you get on
    Last edited by Yongle; Jul 23rd, 2019 at 08:03 AM.

  8. #8
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    [IMG]C:\Users\iqbal.a.11\Desktop\compile error[/IMG]


    Thank You for the detailed explanation.

    The system shows the comment box on right click to the cell successfully, but on clicking on the submit button the system throws comile error : "Expected Function or variable"

  9. #9
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: comment box/ Form for entering comments and it opens as a pop up

    Please find the link to the snapshot

    https://imgur.com/PzY2BMd

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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