Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Comment Summary with Hyperlinks
I want to build a routine which checks all the cells in a workbook t see if any comments exist. I then want to print the cell addresses o each of these cells in a summary sheet AND as hyperlinks. For example: Sheet1!A1 Sheet21B4 Shee3!H678 So when I click onto Sheet1!A1 in this list, it will take me to tha cell. Does anyone know how to do this? Thn -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=27520 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Comment Summary with Hyperlinks
Sub ListComments()
Dim cmt As Comment Dim rng As Range Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet On Error Resume Next Set sh1 = ActiveWorkbook.Sheets("Comments") On Error GoTo 0 If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook .Worksheets.Add(After:=.Worksheets(.Worksheets.Cou nt)).Name = "Comments" Set rng = .Worksheets("Comments").Range("A1") For Each sh In .Worksheets If LCase(sh.Name) < "comments" Then For Each cmt In sh.Comments sStr = cmt.Parent.Parent.Name & "!" & _ cmt.Parent.Address(0, 0) sStr1 = "'" & cmt.Parent.Parent.Name & "'!" & _ cmt.Parent.Address(0, 0) rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", _ SubAddress:=sStr1, _ TextToDisplay:=sStr Set rng = rng.Offset(1, 0) Next End If Next End With End Sub -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I want to build a routine which checks all the cells in a workbook to see if any comments exist. I then want to print the cell addresses of each of these cells in a summary sheet AND as hyperlinks. For example: Sheet1!A1 Sheet21B4 Shee3!H678 So when I click onto Sheet1!A1 in this list, it will take me to that cell. Does anyone know how to do this? Thnx -- ExcelMonkey ------------------------------------------------------------------------ ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221 View this thread: http://www.excelforum.com/showthread...hreadid=275200 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use Hyperlinks inside an Excel cell's comment field? | Excel Worksheet Functions | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
Excel Hyperlinks- cell content v. hyperlinks | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) |