A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel losing hyperlinks when I sort the worksheet



 
 
Thread Tools Display Modes
  #1  
Old November 3rd, 2008, 10:24 PM posted to microsoft.public.excel.links,microsoft.public.excel.programming
Scott Bass
external usenet poster
 
Posts: 1
Default Excel losing hyperlinks when I sort the worksheet

Hi,

(Sorry for the crossposting, but I felt this post applied to both
newsgroups...assuming excel.links means "hyperlinks")

I have an Excel worksheet with about 1000 rows. One of the columns contains
hyperlinks. The text of the hyperlink is the URL - IOW the displayed text
and hyperlink are the same.

When I add new data to the worksheet, I need to sort it by a particular
column. When I do so, some of the hyperlinks are removed. The text is
still blue, underlined, proper font, but the hyperlink is gone (it's a very
hard problem to find).

This happens in both Excel 2003 and Excel 2007.

Has anyone ever seen this behavior, and know of a workaround?

Alternatively, is there an Excel function that will create a hyperlink from
the text in a cell? If so, can you also provide the VB code to:

* spin thru all the rows in the workbook
* for the text in column whatever, apply the function to the cell value
to convert it to a hyperlink

Sorry, while I'm a programmer and can follow and modify VB code, I'm not
fluent in VB so find it hard to create from scratch.

Thanks for the help!

Cheers,
Scott


  #2  
Old November 3rd, 2008, 10:44 PM posted to microsoft.public.excel.links,microsoft.public.excel.programming
Jennifer[_4_]
external usenet poster
 
Posts: 1
Default Excel losing hyperlinks when I sort the worksheet

On Nov 3, 3:24*pm, "Scott Bass" sas_l_739 at yahoo dot com dot au
wrote:
Hi,

(Sorry for the crossposting, but I felt this post applied to both
newsgroups...assuming excel.links means "hyperlinks")

I have an Excel worksheet with about 1000 rows. *One of the columns contains
hyperlinks. *The text of the hyperlink is the URL - IOW the displayed text
and hyperlink are the same.

When I add new data to the worksheet, I need to sort it by a particular
column. *When I do so, some of the hyperlinks are removed. *The text is
still blue, underlined, proper font, but the hyperlink is gone (it's a very
hard problem to find).

This happens in both Excel 2003 and Excel 2007.

Has anyone ever seen this behavior, and know of a workaround?

Alternatively, is there an Excel function that will create a hyperlink from
the text in a cell? *If so, can you also provide the VB code to:

* spin thru all the rows in the workbook
* for the text in column whatever, apply the function to the cell value
to convert it to a hyperlink

Sorry, while I'm a programmer and can follow and modify VB code, I'm not
fluent in VB so find it hard to create from scratch.

Thanks for the help!

Cheers,
Scott


You could put some code in a loop to set the hyperlink value to be
what is the text in the cell like this (assuming Sheet 1 is the
worksheet name and your hyperlinks are in Column F) :

Dim X As Integer

For X = 1 To 1000
If Sheet1.Cells(X, 5).Value "" Then
Sheet1.Hyperlinks.Add Anchor:=Sheet1.Cells(X, 5), Address:= _
Sheet1.Cells(X, 5).Value, TextToDisplay _
:=Sheet1.Cells(X, 5).Value
End If
Next X
End Sub
  #3  
Old November 4th, 2008, 03:10 PM posted to microsoft.public.excel.links,microsoft.public.excel.programming
Bill Manville
external usenet poster
 
Posts: 258
Default Excel losing hyperlinks when I sort the worksheet

Jennifer wrote:
Column F

Column E, I think

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

  #4  
Old November 7th, 2008, 10:40 AM posted to microsoft.public.excel.links,microsoft.public.excel.programming
rslc[_2_]
external usenet poster
 
Posts: 2
Default Excel losing hyperlinks when I sort the worksheet

Hi
this formula will create a clickable link using contents of cell c17 as
the search criteria
not sure if it is what you want but maybe you can modify it to suit
good luck rslc

=HYPERLINK("#'sheetname1'!a" & MATCH(C17,sheetname1!A1:A500,0), C17)


"Scott Bass" sas_l_739 at yahoo dot com dot au wrote in message
...
Hi,

(Sorry for the crossposting, but I felt this post applied to both
newsgroups...assuming excel.links means "hyperlinks")

I have an Excel worksheet with about 1000 rows. One of the columns
contains hyperlinks. The text of the hyperlink is the URL - IOW the
displayed text and hyperlink are the same.

When I add new data to the worksheet, I need to sort it by a particular
column. When I do so, some of the hyperlinks are removed. The text is
still blue, underlined, proper font, but the hyperlink is gone (it's a
very hard problem to find).

This happens in both Excel 2003 and Excel 2007.

Has anyone ever seen this behavior, and know of a workaround?

Alternatively, is there an Excel function that will create a hyperlink
from the text in a cell? If so, can you also provide the VB code to:

* spin thru all the rows in the workbook
* for the text in column whatever, apply the function to the cell
value to convert it to a hyperlink

Sorry, while I'm a programmer and can follow and modify VB code, I'm not
fluent in VB so find it hard to create from scratch.

Thanks for the help!

Cheers,
Scott



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:05 PM.


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