I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Private Sub CommandButton1_Click()
Dim res as String
res = InputBox("Enter you text")
Activesheet.UnProtect Password:="ABCD"
' what you will do with the text
Activesheet.Protect Password:="ABCD"
End Sub

--
Regards,
Tom Ogilvy


"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to
have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Hi Tom

This does bring up a box but if I type anything in it and click the OK
button the text and box vanishes. Also is there a way to have more than 1
line in the text box?

Thanks
John

"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim res as String
res = InputBox("Enter you text")
Activesheet.UnProtect Password:="ABCD"
' what you will do with the text
Activesheet.Protect Password:="ABCD"
End Sub

--
Regards,
Tom Ogilvy


"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to
have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Perhaps I misunderstood what you wanted.

When you protect a sheet, you have 3 things you can protect: Contents,
Objects, Scenarios. If you don't protect contents, you should still be able
to add comments.

--
Regards,
Tom Ogilvy

"John Davies" wrote in message
...
Hi Tom

This does bring up a box but if I type anything in it and click the OK
button the text and box vanishes. Also is there a way to have more than 1
line in the text box?

Thanks
John

"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim res as String
res = InputBox("Enter you text")
Activesheet.UnProtect Password:="ABCD"
' what you will do with the text
Activesheet.Protect Password:="ABCD"
End Sub

--
Regards,
Tom Ogilvy


"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments
or
notes to. However all cells on the sheet are in use. Is it possible
to
have
a command button that if it is clicked will bring up a resizeable box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

John, if you select edit objects when you protect the sheet you will be able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine & _
"New Text Lines are added automatically." & vbNewLine & _
"Leave blank or push ""Cancel"" to exit.", "Please write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to
have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Paul

I copied your code from Sub AddComment() to End Sub and posted it into a
macro but when I try to run the macro it comes up with a compile error. Am I
supposed to paste all the code or is there a bug in the code?

Thanks for your help
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will be able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine & _
"New Text Lines are added automatically." & vbNewLine & _
"Leave blank or push ""Cancel"" to exit.", "Please write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to
have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Hi Tom

Sorry for the confusion. I do not want to save the sheet with the contents
unprotected in case somebody changes some formulas in the cells. What I
really need is a text box that will pop up by clicking a command button even
though the sheet is protected so that I can enter miscellaneous information
into the text box.

Also, would it be possible to run a macro to delete the popup box as it is
not always needed?

Thanks for your help.
John
"Tom Ogilvy" wrote:

Perhaps I misunderstood what you wanted.

When you protect a sheet, you have 3 things you can protect: Contents,
Objects, Scenarios. If you don't protect contents, you should still be able
to add comments.

--
Regards,
Tom Ogilvy

"John Davies" wrote in message
...
Hi Tom

This does bring up a box but if I type anything in it and click the OK
button the text and box vanishes. Also is there a way to have more than 1
line in the text box?

Thanks
John

"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim res as String
res = InputBox("Enter you text")
Activesheet.UnProtect Password:="ABCD"
' what you will do with the text
Activesheet.Protect Password:="ABCD"
End Sub

--
Regards,
Tom Ogilvy


"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments
or
notes to. However all cells on the sheet are in use. Is it possible
to
have
a command button that if it is clicked will bring up a resizeable box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Hi Paul
Please ignore my previous e-mail as I have managed to get the code to run by
deleting some spaces. This is great, but I must ask you a further favour.
Can you tell me whether it is possible to run some code that will delete the
message that is created by your original code as I do not want the message to
remain indefinately on the sheet. Also is it possible to change the colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will be able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine & _
"New Text Lines are added automatically." & vbNewLine & _
"Leave blank or push ""Cancel"" to exit.", "Please write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments or
notes to. However all cells on the sheet are in use. Is it possible to
have
a command button that if it is clicked will bring up a resizeable box that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

When you protect a sheet, you have 3 things you can protect: Contents,
Objects, Scenarios. If you don't protect contents, you should still be
able
to add comments.

Sorry, I meant if you don't protect **objects** rather than don't protect
comments.

If you need code that "holds your hand" to enter a comment, then I guess
Paul has given you that. I personnally wouldn't reinvent the wheel and just
use the comments capability provided with excel.

If you want to alter the appearance of a comment, turn on the macro
recorder, select a comment, right click on it and change the properties you
want. Then turn off the macro recorder. See Debra Dalgleish's site:

See the comment topics under the letter C:
http://www.contextures.com/tiptech.html

--
Regards,
Tom Ogilvy



"John Davies" wrote in message
...
Hi Tom

Sorry for the confusion. I do not want to save the sheet with the contents
unprotected in case somebody changes some formulas in the cells. What I
really need is a text box that will pop up by clicking a command button
even
though the sheet is protected so that I can enter miscellaneous
information
into the text box.

Also, would it be possible to run a macro to delete the popup box as it is
not always needed?

Thanks for your help.
John
"Tom Ogilvy" wrote:

Perhaps I misunderstood what you wanted.

When you protect a sheet, you have 3 things you can protect: Contents,
Objects, Scenarios. If you don't protect contents, you should still be
able
to add comments.

--
Regards,
Tom Ogilvy

"John Davies" wrote in message
...
Hi Tom

This does bring up a box but if I type anything in it and click the OK
button the text and box vanishes. Also is there a way to have more
than 1
line in the text box?

Thanks
John

"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim res as String
res = InputBox("Enter you text")
Activesheet.UnProtect Password:="ABCD"
' what you will do with the text
Activesheet.Protect Password:="ABCD"
End Sub

--
Regards,
Tom Ogilvy


"John Davies" wrote in
message
...
I have a protected spreadsheet that occasionaly I need to add
comments
or
notes to. However all cells on the sheet are in use. Is it
possible
to
have
a command button that if it is clicked will bring up a resizeable
box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

John, try this,

Const Password As String = "123" '*Change Password Here************
Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
'change background and font color
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long
If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & _
vbNewLine & vbNewLine & "New Text Lines are added automatically." & _
vbNewLine & "Leave blank or push ""Cancel"" to exit.", _
"Please write your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment

.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg

'***this will make the back ground light blue
.Shape.Fill.ForeColor.SchemeColor = 41

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


Sub Delete_Comments()
'will delete all comments on the sheet
Msg = "This Will Delete All Comments On The Sheet, Do You Want To Do This ?"
Title = "Are Your Sure"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub
End If

ActiveSheet.Unprotect Password:=Password
ActiveSheet.Cells.ClearComments
ActiveSheet.Protect Password:=Password
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul
Please ignore my previous e-mail as I have managed to get the code to run
by
deleting some spaces. This is great, but I must ask you a further favour.
Can you tell me whether it is possible to run some code that will delete
the
message that is created by your original code as I do not want the message
to
remain indefinately on the sheet. Also is it possible to change the
colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will be
able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As
Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine
& _
"New Text Lines are added automatically." & vbNewLine &
_
"Leave blank or push ""Cancel"" to exit.", "Please write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg =
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments
or
notes to. However all cells on the sheet are in use. Is it possible
to
have
a command button that if it is clicked will bring up a resizeable box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Hi Paul B

Thanks for your help, that works great. Just 1 other thing, please explain
how I can a line to the code to make the message box have a border.


"Paul B" wrote:

John, try this,

Const Password As String = "123" '*Change Password Here************
Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
'change background and font color
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long
If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & _
vbNewLine & vbNewLine & "New Text Lines are added automatically." & _
vbNewLine & "Leave blank or push ""Cancel"" to exit.", _
"Please write your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment

.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg

'***this will make the back ground light blue
.Shape.Fill.ForeColor.SchemeColor = 41

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible? ", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


Sub Delete_Comments()
'will delete all comments on the sheet
Msg = "This Will Delete All Comments On The Sheet, Do You Want To Do This ?"
Title = "Are Your Sure"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub
End If

ActiveSheet.Unprotect Password:=Password
ActiveSheet.Cells.ClearComments
ActiveSheet.Protect Password:=Password
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul
Please ignore my previous e-mail as I have managed to get the code to run
by
deleting some spaces. This is great, but I must ask you a further favour.
Can you tell me whether it is possible to run some code that will delete
the
message that is created by your original code as I do not want the message
to
remain indefinately on the sheet. Also is it possible to change the
colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will be
able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As
Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine
& _
"New Text Lines are added automatically." & vbNewLine &
_
"Leave blank or push ""Cancel"" to exit.", "Please write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg =
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
I have a protected spreadsheet that occasionaly I need to add comments
or
notes to. However all cells on the sheet are in use. Is it possible
to
have
a command button that if it is clicked will bring up a resizeable box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

John, put this below the code to change the background color

'this will make the border around the comment
'red & weight of the line 2
.Shape.Line.Weight = 2#
.Shape.Line.ForeColor.SchemeColor = 10

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul B

Thanks for your help, that works great. Just 1 other thing, please
explain
how I can a line to the code to make the message box have a border.


"Paul B" wrote:

John, try this,

Const Password As String = "123" '*Change Password Here************
Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
'change background and font color
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As
Long
If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & _
vbNewLine & vbNewLine & "New Text Lines are added automatically." &
_
vbNewLine & "Leave blank or push ""Cancel"" to exit.", _
"Please write your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg =
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment

.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg

'***this will make the back ground light blue
.Shape.Fill.ForeColor.SchemeColor = 41

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


Sub Delete_Comments()
'will delete all comments on the sheet
Msg = "This Will Delete All Comments On The Sheet, Do You Want To Do
This ?"
Title = "Are Your Sure"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub
End If

ActiveSheet.Unprotect Password:=Password
ActiveSheet.Cells.ClearComments
ActiveSheet.Protect Password:=Password
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul
Please ignore my previous e-mail as I have managed to get the code to
run
by
deleting some spaces. This is great, but I must ask you a further
favour.
Can you tell me whether it is possible to run some code that will
delete
the
message that is created by your original code as I do not want the
message
to
remain indefinately on the sheet. Also is it possible to change the
colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will
be
able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt
As
Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine &
vbNewLine
& _
"New Text Lines are added automatically." &
vbNewLine &
_
"Leave blank or push ""Cancel"" to exit.", "Please
write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg
=
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain
visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in
message
...
I have a protected spreadsheet that occasionaly I need to add
comments
or
notes to. However all cells on the sheet are in use. Is it
possible
to
have
a command button that if it is clicked will bring up a resizeable
box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

Thanks Paul

To be a real pain can you tell me how to make the text bold, change its size
and make it proper case?

Thanks again

"Paul B" wrote:

John, put this below the code to change the background color

'this will make the border around the comment
'red & weight of the line 2
.Shape.Line.Weight = 2#
.Shape.Line.ForeColor.SchemeColor = 10

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul B

Thanks for your help, that works great. Just 1 other thing, please
explain
how I can a line to the code to make the message box have a border.


"Paul B" wrote:

John, try this,

Const Password As String = "123" '*Change Password Here************
Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
'change background and font color
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As
Long
If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & _
vbNewLine & vbNewLine & "New Text Lines are added automatically." &
_
vbNewLine & "Leave blank or push ""Cancel"" to exit.", _
"Please write your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg =
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment

.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg

'***this will make the back ground light blue
.Shape.Fill.ForeColor.SchemeColor = 41

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


Sub Delete_Comments()
'will delete all comments on the sheet
Msg = "This Will Delete All Comments On The Sheet, Do You Want To Do
This ?"
Title = "Are Your Sure"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub
End If

ActiveSheet.Unprotect Password:=Password
ActiveSheet.Cells.ClearComments
ActiveSheet.Protect Password:=Password
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul
Please ignore my previous e-mail as I have managed to get the code to
run
by
deleting some spaces. This is great, but I must ask you a further
favour.
Can you tell me whether it is possible to run some code that will
delete
the
message that is created by your original code as I do not want the
message
to
remain indefinately on the sheet. Also is it possible to change the
colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you will
be
able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt
As
Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine &
vbNewLine
& _
"New Text Lines are added automatically." &
vbNewLine &
_
"Leave blank or push ""Cancel"" to exit.", "Please
write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg
=
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain
visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in
message
...
I have a protected spreadsheet that occasionaly I need to add
comments
or
notes to. However all cells on the sheet are in use. Is it
possible
to
have
a command button that if it is clicked will bring up a resizeable
box
that
text can be entered into? If so please advise on the code needed.

Thanks in advance for any help.

Regards
John

John, replace this

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

With this, this is all the font properties that you can set, you can take
out the ones you don't need or just leave them in, I don't know how to make
the text in a comment proper case, maybe somebody else will post with that,
if it can be done.

With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
'***this will change the font to red
.ColorIndex = 3
End With


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Thanks Paul

To be a real pain can you tell me how to make the text bold, change its
size
and make it proper case?

Thanks again

"Paul B" wrote:

John, put this below the code to change the background color

'this will make the border around the comment
'red & weight of the line 2
.Shape.Line.Weight = 2#
.Shape.Line.ForeColor.SchemeColor = 10

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in message
...
Hi Paul B

Thanks for your help, that works great. Just 1 other thing, please
explain
how I can a line to the code to make the message box have a border.


"Paul B" wrote:

John, try this,

Const Password As String = "123" '*Change Password Here************
Sub AddComment()
'This code inserts a comment and allows you to enter the multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
'change background and font color
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt
As
Long
If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & _
vbNewLine & vbNewLine & "New Text Lines are added
automatically." &
_
vbNewLine & "Leave blank or push ""Cancel"" to exit.", _
"Please write your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg
=
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment

.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg

'***this will make the back ground light blue
.Shape.Fill.ForeColor.SchemeColor = 41

With Selection.Font
'***this will change the font to red
.ColorIndex = 3
End With

If cmtMsg < "" Then .Shape.TextFrame.AutoSize = True
shCmt = MsgBox("Do you want the comment to remain
visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


Sub Delete_Comments()
'will delete all comments on the sheet
Msg = "This Will Delete All Comments On The Sheet, Do You Want To Do
This ?"
Title = "Are Your Sure"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
Exit Sub
End If

ActiveSheet.Unprotect Password:=Password
ActiveSheet.Cells.ClearComments
ActiveSheet.Protect Password:=Password
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in
message
...
Hi Paul
Please ignore my previous e-mail as I have managed to get the code
to
run
by
deleting some spaces. This is great, but I must ask you a further
favour.
Can you tell me whether it is possible to run some code that will
delete
the
message that is created by your original code as I do not want the
message
to
remain indefinately on the sheet. Also is it possible to change
the
colour
of the message background and text colour.

Sorry for being a pain.

Thanks for your help
Regards
John

"Paul B" wrote:

John, if you select edit objects when you protect the sheet you
will
be
able
to put in comments, or maybe use a macro like this

Sub AddComment()
'This code inserts a comment and allows you to enter the
multi-lined
text
'like using a typewriter.
' Enter one line at a time and it's resized at the end
automatically
'By: Andrew Engwirda
'From http://blog.livedoor.jp/andrewe/ July 27,2005
'Code added to unprotect and protect the sheet
On Error Resume Next
Dim cmtMsg As String, nwLne As String, LneCnt As Long,
shCmt
As
Long

Const Password As String = "123" '*********Change Password
Here************

If TypeName(Selection) < "Range" Then Exit Sub
ActiveSheet.Unprotect Password:=Password
AddLine:
nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine &
vbNewLine
& _
"New Text Lines are added automatically." &
vbNewLine &
_
"Leave blank or push ""Cancel"" to exit.",
"Please
write
your comment")
If LneCnt 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else
cmtMsg
=
nwLne
LneCnt = LneCnt + 1
If nwLne < "" Then GoTo AddLine
cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
Application.ScreenUpdating = False
With ActiveCell
.ClearComments
.AddComment
With .Comment
.Visible = True
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Shadow.Visible = msoFalse
.Shape.Select True
.Text Text:=cmtMsg
If cmtMsg < "" Then .Shape.TextFrame.AutoSize =
True
shCmt = MsgBox("Do you want the comment to remain
visible?
", _
vbYesNo, "Keep comment visible?")
If shCmt = vbNo Then .Visible = False
End With
.Select
End With
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"John Davies" wrote in
message
...
I have a protected spreadsheet that occasionaly I need to add
comments
or
notes to. However all cells on the sheet are in use. Is it
possible
to
have
a command button that if it is clicked will bring up a
resizeable
box
that
text can be entered into? If so please advise on the code
needed.

Thanks in advance for any help.

Regards
John