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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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)
FYI, you can test for a comment:
for example.
If rngCmt.Comment is Nothing then rngCmt.AddComment
rngCmt.Comment.Text strComment
for example.
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.
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)
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.
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.
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?
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?
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?
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial