VBA To Add Comments Based On Cell Text
Jun 5, 2014
I have a VBA that can add a comment based on the cell value, however, I need it to do it for the whole worksheet and not just one cell. I am pretty new to using VBA's, so this is the best code I could find that would perform what I need. If there is a way to have it do run a vlookup, instead of me listing all the cases, that would be great as well.
VB:
Sub AddComment()
Dim strText As String
With Range("s6")
Select Case UCase(.Value)
Case "UPT": strText = "UnPaid Tardy/Late"
[Code] .....
View 5 Replies
ADVERTISEMENT
Aug 4, 2008
I would like help with a formula or VBA that would show comments based on cell values.
For example, if cell A1 has cell value '2143', I want comments to be 'Twinkle Twinkle Little Stars' and if cell D1 has cell value '4567', I want 'Humpty Dumpty Fall on a Wall', and etc. How can I do it? I have about 35 different cell value categories. I appreciate any help you can give me. Thanks in advance for your time and effort.
View 9 Replies
View Related
Aug 16, 2013
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
View 4 Replies
View Related
Jun 16, 2008
I need a macro that will do the following scenarios.
1. I have two workbooks.
a. The first contains a sheet with " Names" listed in column A.
b. The 2nd contains a sheet with "Names" listed similar to first workbook. The only difference is that the 2nd workbook contains 2 other columns, "Age" in B and "Address" in C.
2. Only the 1st workbook is opened.
3. When I click a name in the 1st workbook, it will look for a match with that of the 2nd workbook.
a. If there's a match, it will copy the corresponding "Age" and "Address" and display them as comment to that name in the 1st workbook.
b. Else, it will display a comment that no record was found.
View 3 Replies
View Related
Jul 2, 2007
I manually enter data into inserted comments in a worksheet. Is it possible to run a macro that formats comments to align based on spaces? Ie
Entering "City","State" and "Amount" on row 1 in a comment and entering "Denver", "Colorado" and "5" in the second row would produce those words to align at a predetermined spacing (ie after every space the next word would start 10 characters over)
-----------------
City State Amount
Denver Colorado 5
View 4 Replies
View Related
Nov 8, 2012
I have a situation where textual comments are entered into a cell where the comments are supposed to begin with a date in mm/dd format. Some enter all 5 characters but some enter m / d in if appropriate to the date, e.g., 9/6 vs 09/06.
I'm trying to take the left 5 characters and compare that to the current date - 7 to ID aged comments and highlight that cell. I currently do this by populating a variable LastUpdate (dim as string) with the first 5 characters, then run this IF statement.
If LastUpdate < Date - 7 Then
Range("O" & i).Select
Selection = "Comments over 7 days old."
End If
This works find except in situations where there is a zero for the first character after the "/", such as "11/05." The code doesn't properly calculate the date and assumes it's old. "11/5" works just fine. I thought I could try translating the variable into a proper date. I "borrowed" this formula from another post herein: =DATE(2012, MID(N12,1,FIND("/",N12)-1), MID(N12,FIND("/",N12)+1,2)). This works fine as a formula to translate the characters into a date but I don't know how to do this same thing in vba.
View 5 Replies
View Related
Aug 2, 2008
I currently have some code which reads a stock file, adding up the stock for each individual item (which may be in one or more locations in the warehouse) and then placing the total stock count in a cell with the detail (of how much stock is in each location, the location and the expiry date) and a separate line for each location, in a comment.
The comment is constructed one line at a time as the code finds the stock records, see code below:
With shtWorking.Range(strStartColumn & intRow + 2)
oldComment = .Comment.Text
.Comment.Delete
.AddComment oldComment & vbNewLine & strComment
.Comment.Shape.TextFrame.AutoSize = True
End With
All this works very nicely, but I want to colour individual lines depending on the expiry date. This can be done manually in Excel, but I cannot find a way of colouring individual lines of text in a comment using VBA.
So, does anyone know of a way I can colour individual lines of text in a comment, using VBA?
View 9 Replies
View Related
Dec 14, 2011
I have created a worksheet to be used by a number of people at work and my issue is how to add sufficient instructions for some of the less tech minded users.
I have entered text in the comment box to 'assist', but would like to add either a picture WITH the text or something to the effect of "for further asistance and make the last part a hyperlink.
View 1 Replies
View Related
Dec 28, 2008
I noticed that I have several (@200) comments that when visible displays Cell X Commented by (my work user number) in the lower left corner of the excel window. I'm not sure how to change this so its the company name instead of my work number. I'd be using a loop to go through each comment but need help with the line that would change this part.
View 8 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Mar 22, 2014
Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.
the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.
View 8 Replies
View Related
Aug 9, 2012
I have an Excel 2007 workbook with several sheets, and various cells in several of the sheets have column headers with a description in the same cell enclosed in curly brackets, e.g. Header{Description}. I would like to to keep the column headers in their respective cells, but move (cut) the descriptions along with the brackets to the respective cell comments.
View 1 Replies
View Related
Jul 8, 2009
I have several worksheets that have comments in the cells. Most of the comments i have set to "un-hide" and they can be printed. I now have this one spreadsheet where no matter what I do I cannot print the comments.
I must have something turned on or off but for thwe life of me I cannot figure out what it is. I can see the comments on the screen but when I go to print preview they are not there.
View 2 Replies
View Related
Jan 7, 2013
I have several comments being placed into a pivot table from a macro. However, all of a sudden, when I changed the source of the Pivot table, the comments now do not appear when hovering over the cell. They only appear way off to the right if I click "review," "Show/hide comment" or "show all comments."
View 1 Replies
View Related
Apr 30, 2012
I have inserted a comment on a cell.... what required is the comment should show every time the cell is selected/highlighted with the keyboard - no matter the mouse pointer is on the cell or not.
Whereas, the comments only shows when the mouse pointer touches the cell...
View 4 Replies
View Related
Dec 28, 2006
I have a worksheet with the following in Column E and F respectively:
ID Name
100 aaa
100 bbb
200 ccc
200 ddd
200 eee
300 fff
400 xxx
500 yyy
500 zzz
500 kkk
500 lll
500 mmm
When I input an ID number in Cell A1 and if it is found in the above table, it should bring all the names corresponding to that ID in a cell comment.
The ID Number is repeated in the above table because the names in Colum F are the dependants name for that particular ID.
View 9 Replies
View Related
Jan 2, 2007
I am trying to add comments in a selection of cells and where there is a value then the comment should show the formula value of the cell ie if the formula in cell A1 is = 9 + 10 then the comment should show this. This I have got to work, but if I have a comment already the macro fails and I have tried to get round this but I am not clever enough.
Sub InsertingComments()
Dim r As Range
For Each r In Selection
If r.Comment = True Then
View 9 Replies
View Related
Feb 15, 2007
I have a list on my first worksheet that is sequentially numbered in the first column, and has work activities in the next column. On my second worksheet, I have those sequential activity numbers as the column headers on a new list. Problem is I don't have any room to label the new list by the activity's actual description(second column, first sheet). If I did, I would just use a simple VLOOKUP.
So, what I would like to do is utilize the VLOOKUP to pass the activity description string to a UDF that will create a comment in those column headers. Then when the user mouses over the activity numbers, the respective activity descripiton will pop-up as a comment thus solving my space problem!
View 8 Replies
View Related
Dec 5, 2007
I would like to be able to sum quantities held in comments. I am using the Ozgrid VBA module to sum cells based on a fill color, and it works incredibly. However, sometimes I only want to sum a fraction of a cell's contents, and I don't want to use separate fill colors for all possible combinations, so I am thinking to have the comment tab contain the quantity that I want to sum. Example: employee has a total hrs worked of 8 but 2.75 of those hours are to be allocated to sick pay. Typically employees are restricted to taking benefit hours in increments of 4 or 8, and I have some coding to handle that situation.
View 4 Replies
View Related
Jan 24, 2008
I need help with my current macro so that it will look in several cells (myrange) if comment(s) are found, write it in a particular cell, so that you end up with all comments written in that cell. If no comment are found then search until the end of the range(or until all comments are found). My current problem is that it is repeatedly writing the comments it finds and I end up with the same number of comments in one cell as number of cells in myrange.
View 9 Replies
View Related
Oct 3, 2008
Is it possible to use conditional formatting on a cell if it contains comments.
View 2 Replies
View Related
Apr 29, 2014
I have Main worksheet (sheet 9) and some codes in Sheet 10 which I use as a source for data validation in Sheet 9. So far I have a macro which looks for changes in cells and, when someone chooses a code from the list the macro adds a comment to the cell and populates it with an explanation of the code. The explanations are also in the code worksheet (Sheet 10). It works really nicely but I cannot work out how to remove the comment if the user clears the value in the cell.
My Macro thus far is:
[Code] .....
View 1 Replies
View Related
Jul 9, 2007
I am using a lot of double click routines in my sheet. In order for the sheet to be more user friendly I have used the comments of cells to tell the user what to do. For instance "Double click to choose type" or some such. Everything works great except that after the user selects what they want from the userform and it hides and the focus comes back to the sheet, the comment is in edit mode. While this is not horrible it is not as nice as I would like. I have tried to have my code send the active cell to another cell and then back, but that doesn't seem to work. Does anyone have any ideas on how to keep the comments from going into edit mode?
View 9 Replies
View Related
Apr 16, 2009
I have a spreadsheet with several comments with pictures on cells. I want them to show up when you select the cell, instead of having to put your mouse cursor on it. I found this code, but then it shows the comment on the middle of the spreadsheet. I want the comments to show next to the selected cell.
View 4 Replies
View Related
Jul 15, 2009
I am dealing with large amount of data.
In the first worksheet, there are multiple comments and I need the property of the cell including the comment to be transferred to the second sheet using a formula.
Suppose in "Sheet1" in cell A5 it says "Number of Sales" with a comment "Check everyday".
Know I need to transfer this to say in "Sheet2" in cell A5. So in A5 of Sheet2 I can type
='Sheet1'!A5
This only brings the text and in this example it would say "Number of Sales" without the comment. All i need is for the comment to be joined with the cell.
I do not how to use Visual Basic and need your help
I cannot use Copy Paste in this case because the file is too large.
If there is no comment it should just write the value of the cell and if no value in cell than it should leave it blank. I will attach a file as a example
View 13 Replies
View Related
Sep 12, 2012
I am creating an excel sheet where there is a comments section where the column width is 58 characters. Is it possible for someone putting in comments to have it automatically go to the next row once they enter 55 characters in the row above? I don't want to use wrap text and need to maintain the rows in this section.
View 8 Replies
View Related
Jan 3, 2014
I have a worksheet that has user fillable cells from B11-AR53. I want the user to be able to attach comments to cells and then to be able to print them on secondary sheet. I know that excel already can perform this function, but it references the cell address and I don't like that method. I want to have the comments on the printout indexed and tied to an index number in the cell. Creating another worksheet with the comments only is perfectly fine. I am currently using this formula (below) to index the comments on the worksheet that they are attached to.
Ideally my report/second worksheet would list-
1 - Comment that is indexed as 1 would be here
2 - Comment that is indexed as 2 would be here
....and so forth
Sub CoverCommentIndicator()
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
[Code] .......
View 5 Replies
View Related
Nov 16, 2006
1)After comparison and no difference between the cells
1.1) If the cell selected is blank then there will not be any color
1.2) Else the cell will be colored GREEN
2)After comparison and there is difference between the cells
2.1) Cells wiill be colored RED
However, my code seems to be unable to execute (1.1) . Pls help to see if there's any thing wrong with my code
For K = 1 To 31
For L = 1 To 29
'Do a comparison of the cells
If StrComp(Array_compare(A, 1), Array_compare(A, 2)) = 0 Then
If IsEmpty(Array_compare(A, 1)) Then
Sheets(3).Select
Cells(K, L) = Array_compare(A, 1)
Else
Sheets(3).Select .................
View 9 Replies
View Related
Aug 16, 2007
What I need to do is write a Vba code that will copy the comments (I believe thats the right term, the ones that make a mouseover box and put a red triangle in the corner of the cell) of a cell and paste them into that cell.
View 9 Replies
View Related
