If ActiveCell has a comment then exit sub

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

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
 
If Not ActiveCell.Comment Is Nothing Then Exit Sub

But what if the user wants to edit the existing comment?
Click to expand...

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
 
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!
Click to expand...



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
Click to expand...

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.
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
Click to expand...

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
 
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.
Click to expand...

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.
 
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.
Click to expand...

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
Click to expand...

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


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


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


Regards
Claus B.
Click to expand...

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
 
Hi Howard,

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


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


Regards
Claus B.
--
Click to expand...


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
 
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
 
Hi Howard,

Am Tue, 13 Jan 2015 04:02:10 -0800 (PST) schrieb L. Howard:
Thanks for the example. A good learning tool.
Click to expand...

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


Regards
Claus B.
 
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.
Click to expand...

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.
 
Hi Howard,

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


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.
--
Click to expand...

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
 
Back
Top