Insert Cell Comments Based On Value In Another Workbook
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
ADVERTISEMENT
Jul 25, 2007
I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
View 8 Replies
View Related
Sep 6, 2006
Public preValue As Variant 'Declares a variable to be used in both macros
'This is the first line for a macro that runs when a cell is changed.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Limits the change to 1 cell only
If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
Target.ClearComments 'Clears any existing comment
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format( Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return.
End Sub
'This code sets the variable preValue when a cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Limits code to 1 cell
If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
preValue = Target.Value 'Set the variable preValue to the selected cell's value
End Sub
How do I take the changes made from the first work book and insert them in a cell on the second work book. Where in the code above do I Put this command.
View 9 Replies
View Related
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
View Related
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
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
Apr 20, 2012
The macro below only works for a single cell, how to include merged cells?
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
[Code]....
View 2 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
Jun 20, 2006
I know how to insert, read and manage comments fields in Excel, however I want to be able to refer to a comment in another spreadsheet. For example
Spreadheet One contains a series of figures, against one of which a comment has been attached.
Spreadsheet Two has links pulling information from cells in various spreadsheets including the cell in Spreadsheet One which has the comment.
View 3 Replies
View Related
Jan 16, 2013
is there a non VBA way to insert the workbook name in a cell?
View 1 Replies
View Related
May 23, 2013
I require spreadsheet to insert row based on the Value in Column (C). If The value in column C is < 15 then insert Blank Row below the line.
find attached the speardsheet.
i.e. 01.00am has been booked for 14plt which is LESS than 15 therefore i require excel to insert row so i can book addtional delivery during that hour.
TIME
DELIVERY COMPANY
PLTS
REF
[Code]...
View 2 Replies
View Related
Apr 13, 2007
I have some code that changes the colour of a cell based on the value of that cell, not sure how useful this is as I could just simply use conditional formatting.
I have a pivot table that is constantly changing values, the column is a result of a number of days...not really relevant.... however If the cell contains a value of greater then 7 I want to insert a .JPG of a lil stop sign, if less then 7 then a little green go sign will be insertedThis is what I have so far..just changes cell color.
Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
If oCell.Value > 7 Then
oCell.Interior.ColorIndex = 3
End If
Next
End Sub
View 9 Replies
View Related
Jun 24, 2014
I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).
The Data looks like this:
Column B Column C
12543 2
13456 2
19543 1
I want it to look like this:
Column A Column B
12543
12543
12543
13456
13456
13456
19543
19543
View 2 Replies
View Related
Jun 3, 2014
I have lists of numbers in column A. Based on the number in the cell, I would like to insert RO_ or RP_ before the number. I currently have two buttons for this, one named RO and the other RP and have simply recorded a macro of what I wish to do. However, instead of inserting RO_ or RP_ before the number the macro replaces the hole lot with "RP_10" or "RO_10" as this was the cell i recorded the macro on. How do I make it so it just inserts the Prefix rather than replace the contents of the cell.
Currently my code for one of the buttons is as follows;
[Code] .....
View 1 Replies
View Related
May 16, 2011
Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.
For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.
View 6 Replies
View Related
Jul 14, 2009
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
View 4 Replies
View Related
Jan 18, 2010
I have data validation and conditional formatting set for my sheets. However, I cannot figure out something.
I want a "comment" box (just as if I would click on "add comment") to pop up automatically if the user enters a certain value in a cell.
For example, if cell C4="1" then no further action is required. If cell C4=anything other than "1", then the user would be required to enter a comment. I would also like the comment to autopopulate with the users login id.
I will attach the book I am working on for reference, but I do not have any code written for this as I could not figure out where to start.
View 5 Replies
View Related
Jun 25, 2009
vba to copy and insert row based on cell value
I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example,
Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet.
I would want to copy and insert the entire row.
LABELAB110
2
1
View 15 Replies
View Related
Jul 15, 2004
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.
View 9 Replies
View Related
Nov 8, 2006
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
View 2 Replies
View Related
Jan 20, 2006
There's got to be a way to do this (mail merge style perhaps...) I would like to insert the entry/ies under a heading in a table in one worksheet whenever that heading matches the data entered in a separate worksheet.
Presently I can find no way to do this in Excel.
View 12 Replies
View Related
Mar 27, 2013
Can I have a picture appear next to what is typed in the cell next to it. I have a drop down list of the different pictures I have, I want to select, for example, "star" from the drop down list, can I have a picture of the star appear in cell next to it? I have all the pictures of the words in my drop down list.
View 1 Replies
View Related
Jan 29, 2009
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
View 2 Replies
View Related
Aug 26, 2009
I am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
View 13 Replies
View Related
Oct 5, 2009
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View 9 Replies
View Related
Mar 21, 2014
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
View 9 Replies
View Related
Jan 1, 2007
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
View 6 Replies
View Related
Feb 9, 2007
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time.
For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View 9 Replies
View Related
Apr 1, 2014
I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.
I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?
View 9 Replies
View Related
