Macro to Insert Comment to a cell

[wojo]

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

 

[Guest]

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

 

[Guest]

**Correction**

Replace "Jimbo" (used for testing purposes lol) with strComments

************************************************

 

[wojo]

I do know how to add a button. I will give this a try. Thanks, Jo

 

[wojo]

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

 

[Guest]

Sure, Change the following code

ActiveCell.Comment.Text Environ$("USERNAME") & Chr(10) & "strComment"
To:
ActiveCell.Comment.Text strComment

The Environ$("USERNAME") code looks up the users logon ID

Ta!
Jim Nicholls

************************************************

 

[wojo]

Jim, YOu've been so helpful, here's one more request.
Can I limit the number of characters that the user can enter as a
comment? I would like to limit it to 25 AND all caps would be nice too!

I'll modify the code, as you described. Thanks again.

JO

 

[Guest]

Code below. This is the first time I've helped anyone out !! let me know
if it helps

Sub vbutton()
On Error GoTo err_Handler

Dim strComments As String
strComments = InputBox("Please enter your comments")

Do Until Len(strComments) <= 25
If Len(strComments) > 25 Then
MsgBox "Please limit your comments to 25 characters", vbOKOnly
strComments = InputBox("Please enter your comments")
End If
Loop
If strComments <> "" Then
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text UCase(strComments)
ActiveCell.Offset(1, 0).Select
End If

Err_Handler_Exit:
Exit Sub

err_Handler:
MsgBox Err.Number & " " & Err.Description
GoTo Err_Handler_Exit
End Sub

****************************************************

 

[wojo]

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.

 

[Guest]

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 nichollsjp@(REMOVEME)Hotmail.co.uk
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!!

 

[Dave Peterson]

#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]

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

 

[wojo]

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.

 

[wojo]

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.

 

[wojo]

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

 

[wojo]

Tried to send you an email, I removed the (removeme), but it still was
rejected. Try emailing me at
wojo36@(removeme)yahoo.com

Thanks Jo

 

[Dave Peterson]

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.)

 

[Dave Peterson]

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.

 

[Dave Peterson]

Ps. You could change the name of the function to something more meaningful,
too.