Hi

First post - be gentle!

I am unable to delete Comments from one of my worksheets. When I try the machine just hangs (eggtimer) forever and the only way out is to Ctrl/Alt/Del. It only happens on this one worksheet - Comments on other Excel files delete OK.

I have not knowingly "protected comments" on this one or anything similar. Any ideas as to what has happened?

Thanks
Grockle

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

Any help would be greatly appreciated.

Thanks.

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

Any help would be greatly appreciated.

Thanks.

Hello all.

A bit of a complicated one, sorry, I'll try and make it as clear as possible.

I'm still a novice on macros, and am limited to using the recording tool and then editing what I get, but I can't successfully record exactly what I want to do on this one.
A friend pointed me at something on Ozgrid which is a bit like what I want, but not exactly.
http://www.ozgrid.com/forum/showthread.php?t=60978
I could go and ask there, but as I've never used that board myself, I feel more comfortable asking for help here

In my worksheet, each column (from D onwards) represents an individual week, with the week number in D20.

I use worksheet protection with a password, because alot of different people will use the same sheet. Users are not allowed to do anything on the worksheet, except tick or untick a number of boxes, which all works fine.

I want a macro that I will attach to a button, that will, in summary
1) turn off the worksheet protection
2) open an input box, to allow the user to enter text which will be stored as a comment for cell D20
3) if there is already text in the comment, show that text in the input box, so that the user can edit or delete it
4) I have modified the comment box format (so that it's blue instead of yellow etc) and any changes to the comment should leave the format unchanged.
5) If the user deletes the comment text, they should be left with a blank comment box
6) At the end of the process, worksheet protection should be turned back on using the original password, including if the user tries to do anything else while editing the comment, such as pressing ESC etc.

In detail . . .
1) Turn off worksheet protection, using the password.
Please note, this is NOT a request for help in cracking the password - I know what the password is (for the record, it's "password"), I just want to force the macro to use the password itself (rather than prompting the user to type it in) when removing and applying worksheet protection.
2) Open an input box. If the number of characters can be limited to something like 200, that would be ideal, but if not, that's OK. Have the text that is in the input box stored as the cell comment for D20.
3) if there is already text in the comment, show that text in the input box, so that the user can edit or delete it
4) I have modified the comment box format (so that it's blue instead of yellow etc) and any changes to the comment should leave the format unchanged.
5) If the user deletes the comment text, they should be left with a blank comment box. In other words, there should always be a comment attached to the cell, even if there is no text in the comment. I plan to set the worksheet up initially with blank comments in all the relevant cells.
6) At the end of the process, worksheet protection should be turned back on using the original password, including if the user tries to do anything else while editing the comment, such as pressing ESC etc, or selecting any other cell on the worksheet.

If it's not clear what I'm after, please let me know.

Thanks very much in advance for any help.

I have run these two macros:

1) Enter a comment into any cell by double-clicking
2) Protect entire worksheet at once with a password

The problem that I am having is that since having run the workbook protection macro, the comment macro has been disabled. Upon double-clicking on any cell, a dialogue box comes up that will allow for a comment to be entered, but once typed into the dialogue box and enter is pressed, the comment does not display in the cell.

I have tried correcting the code below but need some help, as I still cannot get it to show comments once they have been typed. I also think I have the user interface bit incorrect. I do not want to have the protection disabled while imputting a comment. Rather, I want to have the protection running continuously through the comment adding process so that no other data is unprotected temporarily, and just allowing for comments as an option, if possible.


Code:

Option Explicit

Public oldRange As Range

Private Function wbookSheetProtection(WorkbookTarget As Workbook) As Boolean
Dim wsheet As Worksheet

For Each ws In wbookTarget.Worksheets
If wsheet.ProtectContents = True Then
Comment.Visible = True
Protect Password:="YourPassword", UserInterFaceOnly:=True
Else

Comment.Visible = True


Exit Function

End If

Next ws

End Function



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next

Dim rng As Range
Set rng = Target(1, 1)
'I wasn't sure if I should add a command in here to have this code recognize the previous function
oldRange.Comment.Visible = False

With rng
If Not .Comment Is Nothing Then

If .Comment.Visible = False Then

Comment.Visible = True
Else

Comment.Visible = False

End If

End If

End With


Set oldRange = Target(1, 1)

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then

cmtText = InputBox("Enter info:", "Comment Info")

If cmtText = "" Then Exit Sub


Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True

Else

If Target.Comment.Text <> "" Then

inputText = InputBox("Enter info:", "Comment Info")

If inputText = "" Then Exit Sub

cmtText = Target.Comment.Text & Chr(10) & inputText


Else

cmtText = InputBox("Enter info:", "Comment Info")

End If


Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True

End If

Cancel = True


End Sub

Any help you can offer would be greatly appreciated, as I am new to writing macros and this has me stumped.


Thanks

I have a workbook that I received from a vendor that when I click in a cell a comment appears. There is no line or comment indicator for each comment. So I went into the tools - options - views - comments section and chose no comments. The comments still appear and when I go to the tool bar - view - comments nothing appears. I checked for any VBA code and didn't see anything. I was able to get around this problem by copy/paste special into a new worksheet, but curiosity is killing this cat. Any ideas?

Can anyone help me create a macro that will copy all comments to another worksheet in the same workbook and allow me to append to that worksheet whenever new comments are added? I am trying to create a history log for all comments that have been reviewed and approved for my worksheet. I need to have the date the comment was created, the text, the workbook name and the name of the person who created the comment.

Thanks for any help you can give me on this. I have been manually copying and pasting the comments into the history log.

Can you help me by editing this code so that every instance of the username
is in bold whether it is a new comment or an add to comment? I would prefer
just the username be in bold but the username,hour,time line being all bold
as it is now is acceptable. I tried just about everything I can think of so
far so HELP!

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim lName As Long

strDate = "ddmmmyy hh:mm"
Username = Application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Username & " " & Format(Now, strDate) & Chr(10)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Username & Format(Now, strDate)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters.Font.Bold = False
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
End If

End Sub

I know how to print all comments at the end but I need a macro that will print the comments with the value of the cell, not the cell number itself. I have a lot of comments; usually phone numbers for people. When I print out the comments, it gives the cell number. I have to look up the cell number and see the person's name in that cell to associate it with the phone number (example below).

A nice macro that would print the persons name and the comment (if any) would be great. Been searching on net but no luck so far.

Example.

I am getting:

Cell D125
212-512-1234

Cell D125 contains Joe Jones. I would like to get:

Joe Jones (or Cell D125 Joe Jones)
212-512-1234

Thanks.