Adding Comments To Many Rows In Vba
Feb 15, 2007Is any way to add automatically comments in VBA
Some combination of "ctrl" with something else.
ADVERTISEMENT
Adding Comments And Formats
Feb 20, 2009I have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
Adding Comments To Row / Column
Mar 8, 2012I have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).
Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub
Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).
The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.
Adding Comments To A Range
Apr 27, 2007I need to identify any cells in a range that do not contain text then colour and comment them. I intended to use the following code, however it doesn't work as I expected it to.
With rRange.SpecialCells(xlCellTypeBlanks)
If .Count > 0 Then
.Interior.ColorIndex = zErrorColour
.AddComment "This cell must contain a value"
End If
End With
The blanks cells are coloured OK but only the first blank cell gets a comment. Is there a reason why I can't add comments to all the cells in the range? And if there is a good reason, then I guess I'm going to have to write a loop to add the comments.
Adding Comments To Cells Via Macro
Oct 8, 2009When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
View 4 Replies View RelatedAdding Comments To A Protected Worksheet
Jan 20, 2010I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels.
When I enabled protection on the sheets, the ability to add comments was taken away.
Is there a way to add comments to an UNLOCKED cell in a PROTECTED worksheet?
Excel 2007 :: Picture Created With Unique Names - Adding Comments To Cell
Oct 23, 2012Excel 2007
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.
How To Hide Rows That Contain Comments
Aug 14, 2009With the help of Venkat1926 I established a macro that successfully hides a number of rows that are unrquired (based on dates):
View 14 Replies View RelatedConcatenate Comments For Rows Above Each Record ID
Sep 29, 2009Unfortunately my strengths do not lie in VBA for Excel. But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often.
I have three columns (Record ID, Comment, Results). The Results column is added to capture concatenation of the Comments column. Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id.
Each comment should stack in order with a carriage return between them. This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results.
Extract Comments To Multiple Rows
Nov 2, 2009I have been provided a spreadsheet which has a Quantity column "H". In this column is the quantity of the items and a note containing the serial numbers of the items. The note has no real format. Here are some examples:
Example 1: Serial numbers: 161702, 161722, 161740, 161741.
Example 2: Dec 2006: s/n 63194
Jan 2007: shipped 1 widget, 52480
Example 3: Sent in 2004: 717, 718
Apr 07: shipped 63522 and 63524.
Oct 30, 2007: K. Grande reported only two widgets onboard, 63522 and 63524. 717 and 718 are missing.
Nov 2007: 160302.
Dec 14, 2007: shipped 161093.
There are thousands of serial numbers on hundreds of rows like this. I am looking for code that would copy a new row to another sheet for each serial number in the comment with the serial number placed in column "J". I don't know if the extraneous text can be filtered out, but if I can at least get everything on a seperate row, I can sort/delete what I don't need a lot faster than doing this manually.
In have uploaded an example with before and after sheets. The After sheet is what I have done manually so does not match the examples I used on the Before sheet.
Seeing Cell Comments On Sheet With Hidden Rows
Jan 3, 2010I have a spreadsheet where i have security set to prevent a user from selecting locked cells. However when a user selects a cell that is in a row at the bottom of the vissible area the comment text can not bee seen. What i need is to determine what the bottom vissible row is and use that to compare the row number of the current row and thus decide if i should scroll down to improve the visibility of the help text for the cell in question.
View 3 Replies View RelatedAdding Comments To Textbox And Having Textbox Keep Updating
Apr 3, 2014I have a form that has three fields (1. Comments (TEXT), 2. Legacy_Comments (TEXT), 3, Comment date (DATE))
Now my users need to keep adding comments to the comment text box, and when they do it automatically adds the date they entered the comment in the Comment date box. Now my problem is that since they keep adding comments to the comment box, I need to keep track of these comments in the Legacy_Comments (Text box).
For example, the First time a user enters a comment into the (1) comment text box it auto populates the date in the comment date box, and then adds the comment and date to the Legacy_Comment box. the end result is (comment,4/3/2014 now lets say a user needs to add a comment to the comments box tomorrow - I want the legacy_Comment box to then read (comment, 4/3/2014; comment2, 4/4/2014, ...., comment(n),date(n)) OR it can be vice-verse, because I just need to keep track of the comments, I am not worried if the new comments are before or after older (yesterdays / the day before yesterdays comments)
How can I write a VBA code that will always add the new comment to the legacy_comment field, without deleting the comments that were entered previously?
Code:
If isnull(me.comment.value) Then
Exit Sub
ElseIf me.comment.value = true Then
me.comment_date.value = date
me.legacy_comment.value = me.comment.value & "," & me.comment_date.value & ";"
me.legacy_comment.value = me.legacy_comment.value & "," & me.comment_date.value & ";"
It adds the comment only the first time, but it does not concatenate the string from yesterday to the string to today. I do not care which order the comments are, meaning if I added a comment today it can be before OR after the comment from yesterday.
Prevent Copy / Paste Cells With Comments Or Allow Paste But Do Not Paste Comments
Feb 4, 2014How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
Adding 2 Rows After Every One Row
Jan 29, 2009I need some help fomulating a VSB code which would add two rows after every one row.
View 7 Replies View RelatedAdding Rows With VBA
Apr 18, 2008We have workook with several worksheets (tabs). The main page has several headings (category), including "Doors and Ceilings", "Walls" etc. There are currently three rows under each heading. When I go to a category (tab) there is a list of issues in column b. I can go to column A of each issue and if I place an "a" that issue will be copied to the main page under that category. The problem is that i only have 3 rows and I need a row added each time an issue is transferred to the cell before it.
Is there some VBA I can use to detect the precence of data anbd add a row?
Adding Two Rows After Every Existing Row
Aug 7, 2009Is there any way to automatically add two new rows after every existing row containing data?
For example
1 A B C
2 D E F
3 G H I
turn into
1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I
Adding Rows With Data?
Mar 14, 2013I have got a spread sheet (Attached) that works out the rankings of youth football teams so that they can be given fixtures against teams of the same ability. I want to use the same system for another age group which has nearly 100 teams in it but I can't get it to work for over 50 teams.
With my basic Excel knowledge I have worked out how to add extra columns with the correct formula, but I can't work out how to add rows with the correct formula to the table that is in cells A12 -C12 down.
Ideally I would also like this table to show the team with the highest ranking in position 1 & in descending order from there - but I would be more than happy just to add the extra rows for now.
Adding Two Rows Together If They Have The Same Value In Column A
Oct 7, 2008I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?
View 5 Replies View RelatedAdding Conditional Rows
Jul 10, 2006I want to be able to have rows/cells generated with a certain text
based on the answer given by a user.
For example, My question in a cell could be "how many groups have you made?".
Now to this if the user answers "3" then I would want three cells below to
ask "what is the name of your first group", "what is the name of your second
group" and so on.
Updating By Adding New Rows
Mar 1, 2008i have rows of data in an accounting worksheet (payments in/out) which needs constant updating by adding new rows. within the row a formula exists in column D such as =SUM(E11:J11) and the next row is =SUM(E12:J12). if i select row 12, right click and select insert, a new blank row 12 appears but without formula in column D. is it possible to insert row with retained formula? i would prefer to add a row from a blank row below current row and have new row appear with above rows formula but this is not vital.
View 12 Replies View RelatedAdding Rows Automatically Using VBA
Jan 3, 2014I have a spreadsheet where I will input details for "incidents" under different headings on one row per entry (I will have a separate tab for each "incident", and each row will relate to each customer affected by this incident)
For every row that is completed I have a "Summary", which is just a section of the same worksheet, that will list all the basic details of the above entries in a format that will be printed and passed to a different department.
As I will be unable to determine whether a particular incident will have 5 entries or 50, I need a way to add a new row to my "Summary" section if and when a row is completed.
I have a formula to populate the data in the "Summary" section automatically, so all I need now is for it to add new rows as and when they are needed, copying the formulas from the original row, so each new "incident" entry is copied each time.
I have created a dummy spreadsheet but I'm not sure how to attach it to this post.
Formula For Adding 12.5% Onto Multiple Rows?
Apr 25, 2014I have a quotation that I am working on that I need to add 12.5% onto each seperate line price.
There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)
Form Development - Adding Rows
Dec 16, 2009I'm creating a spreadsheet to generate a standardized form that I use as a paralegal.
I have 2 things that I don't know how to create....
1 - I want to have y/n questions on the data entry tab. When boxes are clicked Y, it adds a row and language to the form which exists on another tab. I know how to do links and to do if then statements, but I don't know how to make it not take up space when the answer is n. Some of these clauses are pretty long so I don't want to have big blank spaces when the answer is no.
2 - I have certain sections where I need to control the number of rows. I want to have a cell on the data entry tab where I put in the number of rows that I want. The spread sheet would create those rows and I would then enter the data on the data entry tab. On the form tab, the spreadsheet would refer to the cell telling it the number of rows, add those rows, and link them to the data that I entered on the data entry tab.
Adding Rows To Sheets With 3D Referencing?
Jun 13, 2013I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.
My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?
Autofiltering Then Adding Value To Filtered Rows?
Jul 25, 2014I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>"
Cells.AutoFilter Field:=55, Criteria1:="="
Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
Adding Rows And Calculation To Macro
Nov 3, 2008I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52)
GP,2A,5,A,H,11.52
GP,2A,5,B,H,11.82
GP,2A,5,C,H,12.17
GP,2A,5,D,H,12.5
GP,2A,5,E,H,12.88
GP,2A,5,F,H,13.22
GP,2A,5,G,H,13.61
GP,2A,5,J,H,14.18
GP,2A,5,K,H,14.71
GP,2A,5,L,H,15.26
GP,2A,5,M,H,15.83
The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step.
You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.
Adding A Blank Row In Between Excisting Rows
Mar 20, 2009This is what I have:
A-------------B
gfdfd------sjfsdnjsnd
gfdfd------sjfsdnjsnd
This is what I need:
A-------------B
gfdfd------sjfsdnjsnd
....
I just need a black row between every row that I have now. I'm sure its simple but I can't seem to figure it out. I have tried a macro but don't really know how to work them, it just made a new line in the same spot.
Adding Up Differnt Rows Within A Table?
Sep 24, 2009My table is a much larger version of this:
A B
X1283.786
Y1684.379
Z1719.013
X10745.013
Y10860.668
i want excel to work out the total for x y and z. however i have about 100 different things x y and z could be and its a long list so an automatic way would be great.
Adding Multiple Rows With A Macro
Feb 23, 2010I´ve made a simple macro in order to make easier to add new rows in a table, by filling the cells with the new data.
The problem is that according to one of the fields, I´d like that macro to add multiple rows, with exactly the same information but numbering all these rows on this specific field from 1to the number I filled on the respective cell.
For example, I have beyond another fields that I´ve already filled, the field "Parcel" and "Total of Parcels". I filled parcel with number 1 and total of parcels with 5. Then I´d like the macro to add 5 rows with the "total of parcels" cells containing 5 on every row and the field parcel filled with number 1, 2, 3, 4 and 5, for each row created.
Adding Rows To Dropdown Menu
Nov 28, 2011I created a Validation list from 2 to 20. I want to have it (the list) add a row every time you select the proper data.
Example: If I want to add 2 row I would select the 2 in the drop down menu. If I need 5 rows I need to select 5 from the drop down menu.
Is this a VBA code? Or can I use a formula?