Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
I have created a file that will be used by many "non computer" types.
I would like to create a macro that will select the current cell, "Insert Comment", then wait for the user to input the comment. When the user selects ENTER, The macro would then to to the next cell. Additionally, I would not like any text in the comment box, when the user is prompted for the text. I think Excel defaults to the username: Thanks in advance. Jo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
I'm assuming that you know how to place a button ont o a worksheets. Here's
the code to put behind it. Sub vbutton() Dim strComments As String strComments = InputBox("Please enter your comments") ActiveCell.AddComment ActiveCell.Comment.Visible = False ActiveCell.Comment.Text Environ$("USERNAME") & Chr(10) & "Jimbo" ActiveCell.Offset(1, 0).Select 'Moves to the cell below the activecell. If you 'want to move to the cell to the right, use Activecell.Offset(0,1).Select End Sub I know it's not quite what your looking for but it might be ok!? Thanks James Nicholls "wojo" wrote: I have created a file that will be used by many "non computer" types. I would like to create a macro that will select the current cell, "Insert Comment", then wait for the user to input the comment. When the user selects ENTER, The macro would then to to the next cell. Additionally, I would not like any text in the comment box, when the user is prompted for the text. I think Excel defaults to the username: Thanks in advance. Jo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
I do know how to add a button. I will give this a try. Thanks, Jo
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
Hey, I liked that, it's just what I wanted. The resulting comment
includes the username (by default, I think). Is there a way to have the resulting comment be Just the text entered by the user? Thanks bunches! Jo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
WOW! This works great! You have saved me sooooo much time.
Because you have so helpful... I can't help but ask another question (well two actually <g 1. Can the Input box be made smaller, so the user can see the approximate size of 25 characters? The current size allows the user to input the note and gives them an message, "please limit your entry to 25 characters", but then they must re-enter the info. Could get annoying...until they 'learn'. If the box would only allow 25, that would be great. 2. I've asked this on the newsgroups and done searches. Maybe you know a 'work around'. Is there anyway to link comments so that when that cell is linked to another worksheet the comment is also linked? First time helping... and you were GREAT!!! There are so many people like me, that are using Excel and Access, self taught... hunters and peckers until it works. These groups are wonderful. I only started asking for help last month, I'm sure glad I did! PS Once I got a runtime error 1004, but I can't duplicate. I'll post here if I do. Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
1; I'm not sure how to change the dimensions of a comments box. I'm sure it
can be done. I'll have a look into it and get back to you. 2;There's no 'built in' function that I know of to do this. Again, it can be done but would be a bit awkward. How necessary is this? 3;Regarding your txtbox limit, it can be done but would mean designing a userform. I can show you an example of this but it would be difficult to explain online. Maybe you could email me and I'll mail you back an example. Finally, your 1004 error is caused when you try to add comments to a cell that already contains comments. I'll show you how to fix this when you mail me regarding the above. Speak soon!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
#1. You could make a small userform (instead of inputbox) and limit the length
to 25. I created a small userform with a textbox and two buttons. This was the code that was behind the form: Option Explicit Dim blkProc As Boolean Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() 'do the work End Sub Private Sub TextBox1_Change() Dim maxLen As Long maxLen = 25 If blkProc = True Then Exit Sub blkProc = True With Me.TextBox1 If Len(.Text) maxLen Then .Text = Left(Me.TextBox1.Text, maxLen) Beep End If End With blkProc = False End Sub Debra Dalgleish has some instructions for getting started with userforms: http://www.contextures.com/xlUserForm01.html #2. Saved from a previous post: Is a UserDefinedFunction ok? Option Explicit Function EchoComment(FCell As Range) As Variant Application.Volatile Dim TCell As Range Set TCell = Application.Caller If TCell.Comment Is Nothing Then 'do nothing Else TCell.Comment.Delete End If If FCell.Comment Is Nothing Then 'do nothing Else TCell.AddComment Text:=FCell.Comment.Text End If If FCell.Value = "" Then EchoComment = "" Else EchoComment = FCell.Value End If End Function You'd use it like this: =echocomment(a1) The value in A1 would appear in the cell and the comment would get copied, too. The application.volatile is there to update the comments if you change them. (Changing the comment won't make the function run, but it'll catch up with the next recalculation.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wojo wrote: WOW! This works great! You have saved me sooooo much time. Because you have so helpful... I can't help but ask another question (well two actually <g 1. Can the Input box be made smaller, so the user can see the approximate size of 25 characters? The current size allows the user to input the note and gives them an message, "please limit your entry to 25 characters", but then they must re-enter the info. Could get annoying...until they 'learn'. If the box would only allow 25, that would be great. 2. I've asked this on the newsgroups and done searches. Maybe you know a 'work around'. Is there anyway to link comments so that when that cell is linked to another worksheet the comment is also linked? First time helping... and you were GREAT!!! There are so many people like me, that are using Excel and Access, self taught... hunters and peckers until it works. These groups are wonderful. I only started asking for help last month, I'm sure glad I did! PS Once I got a runtime error 1004, but I can't duplicate. I'll post here if I do. Thanks again. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
Linking would be great, but, in the meantime I have created a macro
that copies the comments on all cells, and pastes them to the linked worksheet. This works for this program, but I am also working with another linked program that is read-only to the user. This linked program does not contain any of the comments (yet) and I would like the program to auto-update when the original program has comments added. This is almost, a "maker/breaker" for implementation of my Excel program. Email on it's way. Jo |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
Dave, is #2 an answer on how to "link" the comments with the cells?
How is echocomment different from getcomment? The 'getcomment' from your previous posts works great. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
Dave, is #2 an answer on how to "link" the comments with the cells?
How is echocomment different from getcomment? The 'getcomment' from your previous posts works great. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
If this is how I would "link" the comment , attached to a cell... to
another worksheet where the cell has been linked, where does the =echocomment(a1) go? There are hundreds of linked cells. Not sure how to use this information. Jo |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
If I recall, =getcomment() would just show the comment in the worksheet cell.
This version of =echocomment() actually copies the comment and retrieves the value, too. I think this version of =echocomment() should have been: =GetValueComment() (Sometimes I steal previously posted material and paste without changes--sometimes with minor changes and forget to change names to something more significant.) wojo wrote: Dave, is #2 an answer on how to "link" the comments with the cells? How is echocomment different from getcomment? The 'getcomment' from your previous posts works great. -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
The =echocomment() would be used instead of the link:
So =if(sheet1!a1="","",sheet1!a1) would be replaced with =echocomment(sheet1!a1) You have to do it to each formula. wojo wrote: If this is how I would "link" the comment , attached to a cell... to another worksheet where the cell has been linked, where does the =echocomment(a1) go? There are hundreds of linked cells. Not sure how to use this information. Jo -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Comment to a cell
Ps. You could change the name of the function to something more meaningful,
too. wojo wrote: If this is how I would "link" the comment , attached to a cell... to another worksheet where the cell has been linked, where does the =echocomment(a1) go? There are hundreds of linked cells. Not sure how to use this information. Jo -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro-insert text(personal comment) | Excel Discussion (Misc queries) | |||
Insert comment to Cell | Excel Discussion (Misc queries) | |||
macro to insert a comment | Excel Worksheet Functions | |||
How can I insert an image inside a comment of a cell? | Excel Discussion (Misc queries) | |||
How do I insert a comment when the Insert:Comment menu is greyed? | Excel Discussion (Misc queries) |