Trying to prevent error in the statement

If myAE = "Add" Then

and there is already a comment in the cell and the user tries to Add another comment.

Thanks,
Howard


Sub myAdd_myEdit_myDelete_Comment()
Dim myAdd, myEdit, myAE
Dim MyComment As String
Dim commentCell As Range
Dim cmt As Comment

myAE = InputBox("If adding comment enter ""Add""" _
& vbCr & vbCr & _
"If editing comment enter ""Edit""" _
& vbCr & vbCr & _
"If Deleting comment enter ""Delete""", "Comments")

If myAE = "Add" Then
ActiveSheet.Unprotect Password:=123

Set commentCell = ActiveCell
MyComment = InputBox("Enter your comments", "Comments")

Range(commentCell.Address).AddComment
Range(commentCell.Address).Comment.Text Text:=MyComment

ElseIf myAE = "Edit" Then
MyComment = InputBox("Enter your comments", "Comments")
ActiveCell.Comment.Text Text:=MyComment

ElseIf myAE = "Delete" Then
ActiveCell.Comment.Delete

ActiveSheet.Protect Password:=123
End If

End Sub

If Not ActiveCell.Comment Is Nothing Then Exit Sub

But what if the user wants to edit the existing comment?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Monday, January 12, 2015 at 6:25:48 PM UTC-8, GS wrote:
If Not ActiveCell.Comment Is Nothing Then Exit Sub

But what if the user wants to edit the existing comment?


Well, of all things, that works. I thought for sure I tried that and was not getting the results I needed.

If you try to Add a comment in a cell where there is already one it exits sub, or else you can Edit the text or else you can Delete a comment.

Thanks for the help. I should have been able to do that myself!

Howard



Sub myAdd_myEdit_myDelete_Comment()
Dim myAdd, myEdit, myAE
Dim MyComment As String
Dim commentCell As Range
Dim cmt As Comment

myAE = InputBox("If adding comment enter ""Add""" _
& vbCr & vbCr & _
"If editing comment enter ""Edit""" _
& vbCr & vbCr & _
"If Deleting comment enter ""Delete""", "Comments")

If myAE = "Add" Then
'ActiveSheet.Unprotect Password:=123

Set commentCell = ActiveCell
MyComment = InputBox("Enter your comments", "Comments")

If Not ActiveCell.Comment Is Nothing Then Exit Sub

Range(commentCell.Address).AddComment

Range(commentCell.Address).Comment.Text Text:=MyComment

ElseIf myAE = "Edit" Then
MyComment = InputBox("Enter your comments", "Comments")
ActiveCell.Comment.Text Text:=MyComment

ElseIf myAE = "Delete" Then
ActiveCell.Comment.Delete

'ActiveSheet.Protect Password:=123
End If

End Sub

I see you commented out the protection code. You do know that VBA works
on protected sheets when this parameter...

UserInterfaceOnly = True

...is set, right? Note that this is non persistent between sessions and
so protection must be reset (removed then replaced) at startup!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Monday, January 12, 2015 at 7:57:01 PM UTC-8, GS wrote:
I see you commented out the protection code. You do know that VBA works
on protected sheets when this parameter...

UserInterfaceOnly = True

..is set, right? Note that this is non persistent between sessions and
so protection must be reset (removed then replaced) at startup!

--
Garry




No, I did not know that. Have not heard of it.

How would it be used in this code, perhaps like EnableEvents False/True at the beginning and end?

Howard

No, I did not know that. Have not heard of it.

How would it be used in this code, perhaps like EnableEvents False/True at the beginning and end?

Howard

With some reading, seems it must be in a WorkBookOpen event.

If that is true, I will probably need to stay with the UnProtect/Protect method.

Howard

Hi Howard,

Am Mon, 12 Jan 2015 17:13:24 -0800 (PST) schrieb L. Howard:

ElseIf myAE = "Edit" Then
MyComment = InputBox("Enter your comments", "Comments")
ActiveCell.Comment.Text Text:=MyComment

what if she wants to keep the existing text and add new text?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Mon, 12 Jan 2015 17:13:24 -0800 (PST) schrieb L. Howard:

ElseIf myAE = "Edit" Then
MyComment = InputBox("Enter your comments", "Comments")
ActiveCell.Comment.Text Text:=MyComment

what if she wants to keep the existing text and add new text?


Regards
Claus B.

I was thinking same! I'd rename "Add" "New" and make "Edit" display
existing text for editing...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Monday, January 12, 2015 at 11:12:33 PM UTC-8, Claus Busch wrote:
Hi Howard,

Am Mon, 12 Jan 2015 17:13:24 -0800 (PST) schrieb L. Howard:

ElseIf myAE = "Edit" Then
MyComment = InputBox("Enter your comments", "Comments")
ActiveCell.Comment.Text Text:=MyComment

what if she wants to keep the existing text and add new text?


Regards
Claus B.
--


That was not mentioned.

I messed with it a bit and can get the OLD text to a variable but can't get it to precede the NEW text in the code.

It has to be something like this but this won't fly...

ElseIf myAE = "Edit" Then
oldCmt = ActiveCell.Comment.Text

MyComment = InputBox("Enter your Comment NEW text...", "Comments")
ActiveCell.Comment.Text Text:=oldCmt & " " & Text:=MyComment

Howard

Hi Howard,

Am Tue, 13 Jan 2015 00:30:14 -0800 (PST) schrieb L. Howard:

I messed with it a bit and can get the OLD text to a variable but can't get it to precede the NEW text in the code.

have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Comments"

The suggestion is realised with a modeless userform.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Comments"

The suggestion is realised with a modeless userform.


Regards
Claus B.

Thanks for the example. A good learning tool.

Howard

Hi Howard,

Am Tue, 13 Jan 2015 04:02:10 -0800 (PST) schrieb L. Howard:

Thanks for the example. A good learning tool.

you are welcome. I am always glad to help.
Do you have the latest version with the label above the textbox?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I'd be inclined to go with a userform but it would help if you had some
basic understanding of the simple task of appending, prepending,
inserting, and replacing string values. As for your attempt here...

ElseIf myAE = "Edit" Then
oldCmt = ActiveCell.Comment.Text
MyComment = InputBox("Enter your comments", "Comments", oldCmt)
ActiveCell.Comment.Text Text:=MyComment

...where the existing comment text displays for the user to edit.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'd be inclined to go with a userform but it would help if you had some
basic understanding of the simple task of appending, prepending,
inserting, and replacing string values. As for your attempt here...

ElseIf myAE = "Edit" Then
oldCmt = ActiveCell.Comment.Text
MyComment = InputBox("Enter your comments", "Comments", oldCmt)
ActiveCell.Comment.Text Text:=MyComment

...where the existing comment text displays for the user to edit.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion