Comment Box To Multipul Cells
I've got a macro that takes text from cells and puts them into a comment box.
People have now decided that they want the text on a new sheet as well as in a comment box. To save me having to reload 180 reports is there any way to take the text from the comment box and put into cells. I need a new cell (on a new line) for each line in the comment box. Each comment box holds a maximum of 5 comments, each comment starts on a new line in the box. There can be less though. I can put the code to get it into the comment box if it'll help. Thanks to everyone that's helped me for the past few requests, managers are starting to ask for some stupid things now. N1KO |
Comment Box To Multipul Cells
to adapt ...
Sub test() Dim rng As Excel.Range Dim rngr As Excel.Range Dim rngT As Excel.Range Dim s As String, v, t, r As Long, c As Long On Error Resume Next Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) If Err Then Exit Sub End If On Error GoTo 0 Set rngr = Nuovo_Range(ThisWorkbook) For Each rngT In rng c = 0 s = rngT.Comment.Text v = Split(s, vbLf) rngr.Offset(r, c) = rngT.Address(, , , True) c = c + 1 For Each t In v rngr.Offset(r, c) = t c = c + 1 Next r = r + 1 Next End Sub Function Nuovo_Range( _ Wb As Excel.Workbook, _ Optional Nome_base As _ String = "Res") As Excel.Range Dim b Set Nuovo_Range = Wb.Worksheets.Add.Range("A1") Application.ScreenUpdating = False On Error Resume Next Do Err.Clear b = b + 1 Nuovo_Range.Parent.Name = Nome_base & b Loop While Err Application.ScreenUpdating = True End Function regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "N1KO" wrote: I've got a macro that takes text from cells and puts them into a comment box. People have now decided that they want the text on a new sheet as well as in a comment box. To save me having to reload 180 reports is there any way to take the text from the comment box and put into cells. I need a new cell (on a new line) for each line in the comment box. Each comment box holds a maximum of 5 comments, each comment starts on a new line in the box. There can be less though. I can put the code to get it into the comment box if it'll help. Thanks to everyone that's helped me for the past few requests, managers are starting to ask for some stupid things now. N1KO |
Comment Box To Multipul Cells
You've saved me a lot of work, I've adapted it slightly to change the columns
to rows but other than that its working brilliantly. Thanks "r" wrote: to adapt ... Sub test() Dim rng As Excel.Range Dim rngr As Excel.Range Dim rngT As Excel.Range Dim s As String, v, t, r As Long, c As Long On Error Resume Next Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts) If Err Then Exit Sub End If On Error GoTo 0 Set rngr = Nuovo_Range(ThisWorkbook) For Each rngT In rng c = 0 s = rngT.Comment.Text v = Split(s, vbLf) rngr.Offset(r, c) = rngT.Address(, , , True) c = c + 1 For Each t In v rngr.Offset(r, c) = t c = c + 1 Next r = r + 1 Next End Sub Function Nuovo_Range( _ Wb As Excel.Workbook, _ Optional Nome_base As _ String = "Res") As Excel.Range Dim b Set Nuovo_Range = Wb.Worksheets.Add.Range("A1") Application.ScreenUpdating = False On Error Resume Next Do Err.Clear b = b + 1 Nuovo_Range.Parent.Name = Nome_base & b Loop While Err Application.ScreenUpdating = True End Function regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "N1KO" wrote: I've got a macro that takes text from cells and puts them into a comment box. People have now decided that they want the text on a new sheet as well as in a comment box. To save me having to reload 180 reports is there any way to take the text from the comment box and put into cells. I need a new cell (on a new line) for each line in the comment box. Each comment box holds a maximum of 5 comments, each comment starts on a new line in the box. There can be less though. I can put the code to get it into the comment box if it'll help. Thanks to everyone that's helped me for the past few requests, managers are starting to ask for some stupid things now. N1KO |
All times are GMT +1. The time now is 03:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com