#1   Report Post  
Branko
 
Posts: n/a
Default sorting links


Please, someone have a macro for sorting hyperlinks?
When I sort a selected area with hyperlinks the resulting hyperlinks are
disabled or mixed-up.
I can send an example of the file.

Thanks.
Branko
  #2   Report Post  
Bill Manville
 
Posts: n/a
Default


In previous tests I have not reproduced a problem of hyperlinks not
sorting with the cells containing them.

I would be interested in seeing the file
( Bill_Manville @ compuserve.com , without the spaces)

Which version of Excel are you using?

A fix would be to use the HYPERLINK worksheet function to create your
links. The following macro should change all hyperlinks in a selected
range into HYPERLINK function calls

Sub MakeHyperlinkFunctions()
Dim C As Range
Dim H As Hyperlink
Dim stDest As String
For Each C In Selection.Cells
If C.Hyperlinks.Count 0 Then
Set H = C.Hyperlinks(1)
stDest = H.Address
If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress
If C.HasFormula Then
If Left(C.Formula, 11) < "=HYPERLINK(" Then
C.Formula = "=HYPERLINK(""" & stDest & """," & Mid(C.Formula,
2) & ")"
End If
Else
C.Formula = "=HYPERLINK(""" & stDest & """,""" & C.Text & """)"
End If
End If
Next
End Sub



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Bill Manville
 
Posts: n/a
Default

FWIW the problem did not reproduce when sent to me by email

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #4   Report Post  
Bill Manville
 
Posts: n/a
Default

Further for the record.
Branko sent me a reproduceable example.
I discovered that some cells had 2 hyperlinks attached and that some of
the hyperlinks were associated with multiple cells.

I wrote a macro to resolve those issues, replacing multi-cell
hyperlinks with single cell ones and, where 2 hyperlinks had been in a
cell putting the second hyperlink in a spare cell to the right so that
Branko could decide which one to keep.

The macro went like this:

Sub FixHyperlinks()
Dim C As Range
Dim H As Hyperlink
Dim R As Range
Dim C2 As Range
Dim I As Integer
Dim stDest As String
For Each C In Selection.Cells
For I = C.Hyperlinks.Count To 1 Step -1
Set H = C.Hyperlinks(I)
Set R = H.Range
If R.Address < C.Address Then
' we have a multiple cell hyperlink - note the destination
stDest = H.Address
If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress
H.Delete
Debug.Print "Deleted hyperlink from " & R.Address
For Each C2 In R.Cells
If C2.Hyperlinks.Count = 0 Then
' we removed the hyperlink from this cell
C2.Hyperlinks.Add C2, stDest, , , C2.Text
Debug.Print "Added hyperlink to " & C2.Address
Else
' put the removed hyperlink 2 cells to right
C2.Offset(, 2).Hyperlinks.Add C2.Offset(, 2), stDest, , ,
C2.Text
End If
C2.Style = "Hyperlink"
Next
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5   Report Post  
Branko
 
Posts: n/a
Default


Bill!
Thank you very much, your code solved this difficult problem.

Best regards,
Branko

"Bill Manville" wrote:

Further for the record.
Branko sent me a reproduceable example.
I discovered that some cells had 2 hyperlinks attached and that some of
the hyperlinks were associated with multiple cells.

I wrote a macro to resolve those issues, replacing multi-cell
hyperlinks with single cell ones and, where 2 hyperlinks had been in a
cell putting the second hyperlink in a spare cell to the right so that
Branko could decide which one to keep.

The macro went like this:

Sub FixHyperlinks()
Dim C As Range
Dim H As Hyperlink
Dim R As Range
Dim C2 As Range
Dim I As Integer
Dim stDest As String
For Each C In Selection.Cells
For I = C.Hyperlinks.Count To 1 Step -1
Set H = C.Hyperlinks(I)
Set R = H.Range
If R.Address < C.Address Then
' we have a multiple cell hyperlink - note the destination
stDest = H.Address
If H.SubAddress < "" Then stDest = stDest & "#" & H.SubAddress
H.Delete
Debug.Print "Deleted hyperlink from " & R.Address
For Each C2 In R.Cells
If C2.Hyperlinks.Count = 0 Then
' we removed the hyperlink from this cell
C2.Hyperlinks.Add C2, stDest, , , C2.Text
Debug.Print "Added hyperlink to " & C2.Address
Else
' put the removed hyperlink 2 cells to right
C2.Offset(, 2).Hyperlinks.Add C2.Offset(, 2), stDest, , ,
C2.Text
End If
C2.Style = "Hyperlink"
Next
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Phantom links KG Excel Discussion (Misc queries) 3 March 20th 05 05:41 PM
Links to other files Eric Excel Discussion (Misc queries) 1 February 24th 05 11:49 PM
Manually update links Emma Excel Worksheet Functions 0 February 22nd 05 02:23 PM
HELP!!! Missing Links!!! [email protected] New Users to Excel 2 February 16th 05 04:20 PM
Links in formulas change when another user runs a workbook L Mehl Excel Discussion (Misc queries) 2 November 27th 04 10:27 PM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"