Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
Is there a VB way to copy the comments from a cell in one
workbook and attach them to a cell in a second workbook? Wayne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
workbooks("Book2.xls").Worksheets(2).Range("B9").N oteText Text:= _
Workbooks("Book1.xls").Worksheets(1).Range("A5").N oteText See help on notetext if the length is greater than 255 -- Regards, Tom Ogilvy "Wayne" wrote in message ... Is there a VB way to copy the comments from a cell in one workbook and attach them to a cell in a second workbook? Wayne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
Thanks Tom! Looks like about 95% of what i'll need.
I'd like to do this throughout a range of cells. Here is the code I'm using to step through the range to color the backgound. Each cell contains a paste link reference to the source file. I'm hoping I can include code like yours as part of the "For each cell in range" loop: Sub UpdateStoplights() Application.ScreenUpdating = False Range("stoplights").Select Dim Cel As Range Dim val As Integer For Each Cel In Range("stoplights") val = Cel.Value Select Case val Case 1 Cel.Interior.ColorIndex = 3 Cel.Font.ColorIndex = 3 Case 2 Cel.Interior.ColorIndex = 6 Cel.Font.ColorIndex = 6 Case 3 Cel.Interior.ColorIndex = 4 Cel.Font.ColorIndex = 4 Case 4 Cel.Interior.ColorIndex = 5 Cel.Font.ColorIndex = 5 Case Else Cel.Interior.ColorIndex = 2 Cel.Font.ColorIndex = 2 End Select Next Thanks again. Wayne -----Original Message----- workbooks("Book2.xls").Worksheets(2).Range ("B9").NoteText Text:= _ Workbooks("Book1.xls").Worksheets(1).Range ("A5").NoteText See help on notetext if the length is greater than 255 -- Regards, Tom Ogilvy "Wayne" wrote in message ... Is there a VB way to copy the comments from a cell in one workbook and attach them to a cell in a second workbook? Wayne . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
Since you don't say where to put the comments, here is illustrative code:
Sub UpdateStoplights() Application.ScreenUpdating = False Range("stoplights").Select Dim Cel As Range Dim i as Long Dim val As Integer i = 1 For Each Cel In Range("stoplights") workbooks("Book2.xls").Worksheets(2) _ .Range("B9")(i).NoteText Text:= _ cel.NoteText i = i + 1 val = Cel.Value Select Case val Case 1 Cel.Interior.ColorIndex = 3 Cel.Font.ColorIndex = 3 Case 2 Cel.Interior.ColorIndex = 6 Cel.Font.ColorIndex = 6 Case 3 Cel.Interior.ColorIndex = 4 Cel.Font.ColorIndex = 4 Case 4 Cel.Interior.ColorIndex = 5 Cel.Font.ColorIndex = 5 Case Else Cel.Interior.ColorIndex = 2 Cel.Font.ColorIndex = 2 End Select Next -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
I need to copy them from a cell in a second worksheet,
the address of which is in the target cell as a Paste link, e.g "\\FIL-NW02-06 \MPTTechTeamLeaders\8_Tech_Team_Scorecard\Reddy Team Scorecards\[2005_Define_Scorecard_Hixson.xls" So how might that work? Is there a way to get that pathname and useit where you have the "Book.xls" referrence for example? Wayne -----Original Message----- Since you don't say where to put the comments, here is illustrative code: Sub UpdateStoplights() Application.ScreenUpdating = False Range("stoplights").Select Dim Cel As Range Dim i as Long Dim val As Integer i = 1 For Each Cel In Range("stoplights") workbooks("Book2.xls").Worksheets(2) _ .Range("B9")(i).NoteText Text:= _ cel.NoteText i = i + 1 val = Cel.Value Select Case val Case 1 Cel.Interior.ColorIndex = 3 Cel.Font.ColorIndex = 3 Case 2 Cel.Interior.ColorIndex = 6 Cel.Font.ColorIndex = 6 Case 3 Cel.Interior.ColorIndex = 4 Cel.Font.ColorIndex = 4 Case 4 Cel.Interior.ColorIndex = 5 Cel.Font.ColorIndex = 5 Case Else Cel.Interior.ColorIndex = 2 Cel.Font.ColorIndex = 2 End Select Next -- Regards, Tom Ogilvy . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying comments from one cell to another
You would have to open it
Dim bk as Workbook set bk = workbooks.Open( Range(SomeCell).Value) then workbooks("Book2.xls").Worksheets(2) _ .Range("B9")(i).NoteText Text:= _ cel.NoteText would be bk.Worksheets(2) _ .Range("B9")(i).NoteText Text:= _ cel.NoteText -- Regards, Tom Ogilvy wrote in message ... I need to copy them from a cell in a second worksheet, the address of which is in the target cell as a Paste link, e.g "\\FIL-NW02-06 \MPTTechTeamLeaders\8_Tech_Team_Scorecard\Reddy Team Scorecards\[2005_Define_Scorecard_Hixson.xls" So how might that work? Is there a way to get that pathname and useit where you have the "Book.xls" referrence for example? Wayne -----Original Message----- Since you don't say where to put the comments, here is illustrative code: Sub UpdateStoplights() Application.ScreenUpdating = False Range("stoplights").Select Dim Cel As Range Dim i as Long Dim val As Integer i = 1 For Each Cel In Range("stoplights") workbooks("Book2.xls").Worksheets(2) _ .Range("B9")(i).NoteText Text:= _ cel.NoteText i = i + 1 val = Cel.Value Select Case val Case 1 Cel.Interior.ColorIndex = 3 Cel.Font.ColorIndex = 3 Case 2 Cel.Interior.ColorIndex = 6 Cel.Font.ColorIndex = 6 Case 3 Cel.Interior.ColorIndex = 4 Cel.Font.ColorIndex = 4 Case 4 Cel.Interior.ColorIndex = 5 Cel.Font.ColorIndex = 5 Case Else Cel.Interior.ColorIndex = 2 Cel.Font.ColorIndex = 2 End Select Next -- Regards, Tom Ogilvy . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Comments | Excel Discussion (Misc queries) | |||
copying comments box | Excel Worksheet Functions | |||
Copying comments from one cell to another, in a different spreadsheet | Excel Worksheet Functions | |||
Q: copying comments | Excel Discussion (Misc queries) | |||
copying comments | Excel Discussion (Misc queries) |