Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links,microsoft.public.excel.programming
external usenet poster
 
Posts: 9
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   Report Post  
Posted to microsoft.public.excel.links,microsoft.public.excel.programming
external usenet poster
 
Posts: 4
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   Report Post  
Posted to microsoft.public.excel.links,microsoft.public.excel.programming
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links,microsoft.public.excel.programming
external usenet poster
 
Posts: 13
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



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
enter formula into Excel worksheet without losing numbers ricki racer Excel Discussion (Misc queries) 1 July 29th 06 11:13 PM
Is there a way to sort without losing individual set formulas? Rachael-R Excel Worksheet Functions 2 June 22nd 06 10:40 AM
How do I sort rows w/o losing a picture nbCLE Excel Worksheet Functions 1 May 22nd 06 08:01 PM
Losing hyperlinks RedChip Excel Discussion (Misc queries) 0 November 30th 05 11:56 AM
Copying HTML into Excel Losing Hyperlinks Davud Levitt Excel Discussion (Misc queries) 2 October 31st 05 02:02 AM


All times are GMT +1. The time now is 01:10 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"