Editing a Comment through a UserForm

Cook09
Cook09 used Ask the Experts™
on
Attached is a generic workbook of something that  is currently being worked on.  Some of the functionality is not there, and it's not as dynamic as the original, but I think there is enough there for someone to look at to determine what is occurring without having to write a lot of detail.  It's just a very stripped down Sandbox.

Actually, there are three or four questions that I have, but we'll address those one at a time.  Most of the questions happen in the frmHistology SheetCode.  The first two are connected with btn_Submit_click.  Specifically, it works fine until it gets to StringComment:

The first two are somewhat related.  As the UserForm is shown, the values attempt to track the information that is located on the Worksheet by IDX number.  All of that works fine, at least in the original, except I would like to have the  Cell Comments from the logged on User Class to be displayed in the appropriate ListBox2 and TextBox1 on the UserForm. These are  [nADGroup] = "User" or [nADGroup] <> "User" found in Worksheet("Database") A1.

The code works fine for grabbing the contents of the UserForm and placing it within the comment section of the appropriate cell, but how can the reverse be accomplished?

The second part involves adding additional information to a cell that already has a comment in it.  It was mentioned previously that using .....rngCmt.Comment instead of ...rngCmt.AddComment is the procedure for making that happen.  However, it now errors out when just the .Comment is used with the rngCmt.  The AddComment works fine, as long as the cell Comment is empty.  What is it about the range rngCmt that causes it to error out.

The goal is to bring the appropriate Cell Comment into the ListBox2 and TextBox1, or at the very least TextBox1, make any editing changes and then save it back to the same Cell Comment.

 -Utilization-Sandbox.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I was able to get Excel to not automatically error out with just the rngCmt.Comment in the procedure,and that was to insert rngCmt.Name.Comment.  It still doesn't work.  It seems that to get this to work, the Comment may have to be deleted and then reinserted after editing.  Or, maybe Comment should be treated as an Object somehow?

Author

Commented:
It also seems that I've found a work around to bringing in and editing Comments.  As mentioned earlier, bring the Comments into the TextBox, delete the current comments and then re-Add the new one's.   There may be a better solution, this seems somewhat crude, but it'll do for now.
frmHistology.TextBox1.Text = rngCmt.Comment.Text
             strComment = GetAllItems(Me.ListBox2, vbLf)
             MyString = GetTextItems(Me.TextBox1)
             MyString = Replace(MyString, Chr(13), "")
             If Me.ListBox2.ListCount < 1 Then
               rngCmt.ClearComments
               rngCmt.AddComment (MyString)

             Else
             rngCmt.ClearComments
            rngCmt.AddComment (strComment & Chr(10) & Chr(10) & MyString)

Open in new window

Most Valuable Expert 2011
Top Expert 2011
Commented:
FYI, you can test for a comment:
If rngCmt.Comment is Nothing then rngCmt.AddComment
rngCmt.Comment.Text strComment

Open in new window


for example.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Rory,
Once again you've taken an apparent issue with a multiple line work around and distilled it to it's lowest common denominator.

Yes, it worked perfectly.  I thought I had documented the first time, but even at re-looking at it yesterday, it didn't seem to work.  Your code above, takes the comment and replaces it with whatever is currently in the text box.
After spending two or three hours on it yesterday, I replayed some of the code.  While I don't know why it rejected the use of  just .Comment, erroring out on .Comment.Text was due to my inserting an " = " sign after .Text.  Your code doesn't and it worked.

I do have a couple of follow-up questions:
1. Is there a rule of thumb on when to use an " = " sign versus just the variable or input string?.  
When I go to the Object Browser, the initial Range.Comment's Icon is B&W, while the AddComment is Green. In moving to the  Comment Class, .Comment has a Green Icon next to it, with the .Text B&W.  Do either of these correlate to what is required after it, to work properly?   When I look at examples of both, they seem the same, except maybe if it's Boolean there is an "=" sign.

2. While the rngCmt.Comment.Text strComment does overwrite the current contents, how does one just  Append a Comment. That may have been covered in a previous post, I will look though.
The Function shows ([Text], [Start], [Overwrite])  as properties of Text, and I assume Overwrite is the default, would one use Comment.Text ([Text], , Append) to accomplish this?

Below is the Final Comment Box Code based upon your code.  I will add additional lines to check to see if there is anything in Lisbox2, which for the Director it will normally be empty. If so, then I'll leave out the strComment and just use MyString.
strComment = GetAllItems(Me.ListBox2, vbLf)
   MyString = GetTextItems(Me.TextBox1)
   MyString = Replace(MyString, Chr(13), "")
rngCmt.Comment.Text ("Procedures:" & Chr(10) & strComment & Chr(10) & Chr(10) & MyString)

Open in new window

Most Valuable Expert 2011
Top Expert 2011

Commented:
1. Yes. Text should have a green icon in the Comment class as it is a Method and not a Property. For properties you use = because you are assigning one value. Methods can take multiple arguments and do not require =

2. The Text method has three possible arguments:
Text - the text to be added
Start - The character number where the text will be placed (if left blank, all text is replaced)
Overwrite - True to overwrite, False to insert. False is the default.

Author

Commented:
Now its beginning to make a little sense.

One more thing for this one:  If one wanted to "Start" several lines down how would that be written?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Not sure what you mean - you can prefix the comment text with several vbLf entries to create new lines?

Author

Commented:
I was looking at Start, in case Text needed to be placed between two categories.  In this case, let's assume between Procedures and Director's Debrief.  The vbLf would move the entire comment down.  But, how to place it at a specific spot, without redoing the GetAllItems?

Suppose Comment already had two items from the ListBox, a space, then the Director's Debrief. Instead of putting the first two procedures  back in ListBox2 and then Adding an additional one, and then running GetAllItems; it would seem to be easier to just add the One new Procedure between the two categories.  Does it have that capability?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes - you would need to work out where the insertion point should be and specify that as the Start value and False for overwrite.

Author

Commented:
Okay, Thanks.

Probably next week, I've got to post a question on using Excel, probably using Outlook, to automatically send out an invoice once a value is placed in the Cost Col.  RDB has information regarding this, but your solutions have always seemed to be easier to use and understand.

Author

Commented:
Thanks for helping me keep this short.
Most Valuable Expert 2011
Top Expert 2011

Commented:
My solutions for Excel and email usually involve a link to Ron's site... :)

Author

Commented:
At least I'm starting at the right place prior to clarifications...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial