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

A

amircse0711

New Member
Joined
Jul 22, 2019
Messages
25
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Mon[/TD]
[TD="bgcolor: #E2EFDA"]Tue[/TD]
[TD="bgcolor: #E2EFDA"]Wed[/TD]
[TD="bgcolor: #E2EFDA"]Thu[/TD]
[TD="bgcolor: #E2EFDA"]Fri[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]9 - 9:30[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]9:30 - 10:15[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]10:15 -10:30[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]10:30 - 11:15[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]11:15 - 12 noon[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]noon - 1.30[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]1.30 - 2:15[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]2:15 - 2.30[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]3:30 - 3:15[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD="bgcolor: #DDEBF7"]3:15 - 4[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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/ar...nd-notes-75a51eec-4092-42ab-abf8-7669077b7be3
 
Last edited:
Upvote 0
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".
 
Upvote 0
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?

​
 
Upvote 0
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)
 [I]   ' [COLOR=#ff0000]amend range[/COLOR] to the one that matches your timetable[/I]
    If Not Intersect(Target, [[COLOR=#ff0000]B2:H8[/COLOR]]) 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

[I][COLOR=#006400]'create string for comment (amend to suit your own requirements)[/COLOR][/I]
    txt = tb_Comments & N & tb_Time & N & tb_Place & N & tb_Result & N & tb_LineNumber
    
[I][COLOR=#006400]'insert comment in cell[/COLOR][/I]   
    With CommentCell
        On Error Resume Next
        .Interior.Color = 13158600    [I][COLOR=#a9a9a9]'= grey[/COLOR][/I]
        .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
 
Upvote 0
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!
 
Upvote 0
:oops: 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:
Upvote 0



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"
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Adding a Log from user entered comments
Replies
3
Views
510
Tupe77
T
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
142
AlphaFrog
AlphaFrog
E
  • Question Question
Creating data entry form
Replies
3
Views
402
EssKayKay
E
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
710
Sheik
S
C
  • Solved
Adding an editable pop-up
Replies
3
Views
640
ChaosPup
C

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top