Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
Hi everyone.
There is a text on a cell and hyperlink of a website linked to it. I would like to have a macro so that when I chose the cell and run the macro, it reads what the hyperlink is and replace the text in the cell with the actual web address that the hyperlink has. I can do the same by right clicking and chosing edit hyperlink, then copy the hyperlink and paste it. But there are hundreds of cells like this so I need a macro for it. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
Select cells with hyperlinks and run:
Sub ChangeFriendly() Dim r As Range For Each r In Selection With r.Hyperlinks(1) .TextToDisplay = .Address End With Next End Sub -- Gary''s Student - gsnu200908 "Jack" wrote: Hi everyone. There is a text on a cell and hyperlink of a website linked to it. I would like to have a macro so that when I chose the cell and run the macro, it reads what the hyperlink is and replace the text in the cell with the actual web address that the hyperlink has. I can do the same by right clicking and chosing edit hyperlink, then copy the hyperlink and paste it. But there are hundreds of cells like this so I need a macro for it. Can anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
I modified Gary's code so it does every hperlink on the activesheet Sub ChangeFriendly() Dim r As Range For Each r In ActiveSheet.Cells If r.Hyperlinks.Count 0 Then With r.Hyperlinks(1) TextToDisplay = .Address End With End If Next End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147419 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
First off, you missed the "dot" in front of the TextToDisplay property.
Second, you do NOT want to range over all the cells on the ActiveSheet... at minimum, using your approach, I would restrict the range to the ActiveSheet.UsedRange range of cells. However, even that is processing way too many cells. Just loop through the hyperlinks themselves... Sub ChangeFriendly() Dim H As Hyperlink For Each H In ActiveSheet.Cells.Hyperlinks H.TextToDisplay = H.Address Next End Sub -- Rick (MVP - Excel) "joel" wrote in message ... I modified Gary's code so it does every hperlink on the activesheet Sub ChangeFriendly() Dim r As Range For Each r In ActiveSheet.Cells If r.Hyperlinks.Count 0 Then With r.Hyperlinks(1) TextToDisplay = .Address End With End If Next End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147419 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
I believe in most cases there is little difference between Cells and UsedArrea. VBA wil not go outside the used area unless data on the worksheet at one time outside the used area that was deleted. After I made the change I considered changing the Cells to Usedarea and felt it wasn't necessary. VBA expands the Usedarea as the range of cells expands, but doesn't reduced this range unless you delete row and/or columns. that is why there is a number of posting asking why the wrokbook have grown in size and how to get condense a workbook. I didn't like Gary's solution if there was 100 or more hyperlinks. Do you want to select 100 hyperlinks that was randomly scattered on the worksheet. Gary probably got an error when he tried his code on cells that didn't have a hyperlink and couldn't figure out how to avoid the error so he told Jack to select the cells he wanted changed. I knew there wa a count that could be used to avoid the error. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147419 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink copy paste
I believe in most cases there is little difference between Cells and
UsedArrea. VBA wil not go outside the used area unless data on the worksheet at one time outside the used area that was deleted. That is simply not true. Yes, certain Excel functions and methods won't look outside of the UsedRange, but if you set up a loop to iterate all the cells on the worksheet, then VB will iterate all the cell on the worksheet (after all, how would it know you don't intend to do something with a cell currently not in the UsedRange).. Here is your code modified to maintain a counter (just to make sure you won't have to wait too long) and when the counter reaches 500, it will display the counter value and the cell it is currently at. Run this code on a blank sheet (UsedRange being nothing) and watch the cell value that is displayed... it will be a value outside of the UsedRange. Sub ChangeFriendly() Dim r As Range, Counter As Long, TextToDisplay As String For Each r In ActiveSheet.Cells ' ************** Start Added Code ************** Counter = Counter + 1 If Counter = 500 Then MsgBox Counter & " - " & r.Address Exit For End If ' ************** End Added Code ************** If r.Hyperlinks.Count 0 Then With r.Hyperlinks(1) TextToDisplay = .Address End With End If Next End Sub -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy / Paste Hyperlink, but not Formats | Excel Discussion (Misc queries) | |||
Hyperlink Copy & Paste | Excel Discussion (Misc queries) | |||
Copy picture and paste hyperlink | Excel Worksheet Functions | |||
Copy/Paste Hyperlink Address | Excel Discussion (Misc queries) | |||
hyperlink copy / paste | Excel Discussion (Misc queries) |