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

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

Not advised since it can be handled in 1 place instead of every
procedure that needs to act on a sheet.

Also, I note that your password is hard-coded! Better to use a
constant.

I recommend using an m_OpenClose module where you use Auto_Open and
Auto_Close instead of the Workbook open/close events...

Option Explicit
Const msModule$ = "m_OpenClose"

Const gsPWD$ = " " 'edit to suit


Sub Auto_Open()
' Startup code
ResetProtection
End Sub

Sub ResetProtection
Dim wks
For Each wks In ThisWorkbook.Sheets
wks.Unprotect gsPWD
wks.Protect Password:=gsPWD: UserInterfaceOnly = True
Next 'wks
End Sub

Sub Auto_Close()
' Shutdown code

End Sub

--
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 10:18:47 PM UTC-8, GS wrote:
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

Not advised since it can be handled in 1 place instead of every
procedure that needs to act on a sheet.

Also, I note that your password is hard-coded! Better to use a
constant.

Okay, this is all new ground for me and the OP is wanting to add, edit or delete comments on a sheet, as easily as she can. She will be the messenger with the code to her people and is at the edge of Excel knowledge with the code I posted here.

I would be reluctant to step into this "new to me" UserInterfaceOnly at this time on this macro.

Howard.

On Monday, January 12, 2015 at 10:18:47 PM UTC-8, GS wrote:
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

Not advised since it can be handled in 1 place instead of every
procedure that needs to act on a sheet.

Also, I note that your password is hard-coded! Better to use a
constant.

Okay, this is all new ground for me and the OP is wanting to add,
edit or delete comments on a sheet, as easily as she can. She will
be the messenger with the code to her people and is at the edge of
Excel knowledge with the code I posted here.

I would be reluctant to step into this "new to me" UserInterfaceOnly
at this time on this macro.

Howard.

Food for thought...

Bad habits and innefficiency have no place at all, regardless of skill
level! This especially holds true when we provide 'turnkey' code for
those who don't know where to begin!!!

--
Garry

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

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

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

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

Do you have the latest version with the label above the textbox?



Yes, the Label that informs to use CTRL + Enter for new line?

I do have a couple questions...

The Private Sub cmdOK_Click() code has:
UserForm1.txtCom.Enabled = False

And Private Sub cmdCancel_Click() code has:
.Enabled = False

But neither button Click dismiss the UserForm from the sheet.

I have to use the Red X cancel to close the UserForm.

I'm unsure how to fix that if indeed it requires fixing.

Howard

Hi Howard,

Am Tue, 13 Jan 2015 09:18:56 -0800 (PST) schrieb L. Howard:

But neither button Click dismiss the UserForm from the sheet.

I thought that the user wants to edit or add more than one comment.
Therefore I made the UserForm modeless and visible until it is closed
with the red X. But you can add a button to hide the UserForm


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

On Tuesday, January 13, 2015 at 9:29:00 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Tue, 13 Jan 2015 09:18:56 -0800 (PST) schrieb L. Howard:

But neither button Click dismiss the UserForm from the sheet.

I thought that the user wants to edit or add more than one comment.
Therefore I made the UserForm modeless and visible until it is closed
with the red X. But you can add a button to hide the UserForm


Regards
Claus B.
--

Aa Ha! Now it works and makes sense to me.

Bring up the UserForm and Add, Edit or Delete any or all comments on the sheet.

I'll most likely put the Hide-When-Done button on it.

Also, my archive system is not as good as it should be, because I was trying to find a "Comment Making" solution from you having to do with a weekly assigning Desk times, or Room Reservations for a number of people. It too used a UserForm and I know I have it in my archive.

Thanks,
Howard

Hi Howard,

Am Tue, 13 Jan 2015 11:50:43 -0800 (PST) schrieb L. Howard:

Also, my archive system is not as good as it should be, because I was trying to find a "Comment Making" solution from you having to do with a weekly assigning Desk times, or Room Reservations for a number of people. It too used a UserForm and I know I have it in my archive.

oh yes, I remember.
Please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "DeskBookings_2.0"


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

oh yes, I remember.
Please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "DeskBookings_2.0"


Regards
Claus B.
--

Yep, that's it!

Howard

On Monday, January 12, 2015 at 10:18:47 PM UTC-8, GS wrote:
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

Not advised since it can be handled in 1 place instead of every
procedure that needs to act on a sheet.

Also, I note that your password is hard-coded! Better to use a
constant.

Okay, this is all new ground for me and the OP is wanting to add,
edit or delete comments on a sheet, as easily as she can. She will
be the messenger with the code to her people and is at the edge of
Excel knowledge with the code I posted here.

I would be reluctant to step into this "new to me" UserInterfaceOnly
at this time on this macro.

Howard.

My point is that you don't have any error handling code in your
procedure[s]! Sooo.., should an error occur then your sheet gets left
unprotected. Using my suggestion at startup obviates the need for extra
error handling code IN EVERY PROCEDURE where you toggle protection to
make changes. Otherwise, I suggest adding error handling!

Optionally, you can specify which sheets get protected and what level
of protection is applied to each sheet. The latter, though, is a bit
more complex than the simple code I exampled.

--
Garry

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