Avatar of Cook09
Cook09Flag for United States of America

asked on 

Editing a Comment through a UserForm

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Cook09
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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?
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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

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.
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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?
Not sure what you mean - you can prefix the comment text with several vbLf entries to create new lines?
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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?
Yes - you would need to work out where the insertion point should be and specify that as the Start value and False for overwrite.
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

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.
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

Thanks for helping me keep this short.
My solutions for Excel and email usually involve a link to Ron's site... :)
Avatar of Cook09
Cook09
Flag of United States of America image

ASKER

At least I'm starting at the right place prior to clarifications...
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo