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

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(xlCellTypeComments)
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

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(xlCellTypeComments)
> 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