Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink Screentips
Hi
In Column H, I have webpage hyperlinks. In edit the screentip as some text, some between Brackets (***) I'd like to run a macro that goes down column H and gets all the chacracters between and put them in column C. So H1 would go into C1 Etc. Thanks in advance Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink Screentips
One way is to use a UDF:
Option Explicit Function GetHyperlinkScreenTip(Rng As Range) As String Application.Volatile Dim myStr As String Set Rng = Rng(1) If Rng.Hyperlinks.Count = 0 Then myStr = "" Else myStr = Rng.Hyperlinks(1).ScreenTip End If Do If Left(myStr, 1) = "*" Then myStr = Mid(myStr, 2) Else Exit Do End If Loop Do If Right(myStr, 1) = "*" Then myStr = Left(myStr, Len(myStr) - 1) Else Exit Do End If Loop GetHyperlinkScreenTip = Trim(myStr) End Function So if you had a hyperlink in A1, you could put =GetHyperlinkScreenTip(a1) in that adjacent cell. Be aware that if you change the hyperlink, then this formula cell won't change until your workbook calculates. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =GetHyperlinkScreenTip(a1) DavidM wrote: Hi In Column H, I have webpage hyperlinks. In edit the screentip as some text, some between Brackets (***) I'd like to run a macro that goes down column H and gets all the chacracters between and put them in column C. So H1 would go into C1 Etc. Thanks in advance Dave -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink Screentips
Thanks Dave
"Dave Peterson" wrote in message ... One way is to use a UDF: Option Explicit Function GetHyperlinkScreenTip(Rng As Range) As String Application.Volatile Dim myStr As String Set Rng = Rng(1) If Rng.Hyperlinks.Count = 0 Then myStr = "" Else myStr = Rng.Hyperlinks(1).ScreenTip End If Do If Left(myStr, 1) = "*" Then myStr = Mid(myStr, 2) Else Exit Do End If Loop Do If Right(myStr, 1) = "*" Then myStr = Left(myStr, Len(myStr) - 1) Else Exit Do End If Loop GetHyperlinkScreenTip = Trim(myStr) End Function So if you had a hyperlink in A1, you could put =GetHyperlinkScreenTip(a1) in that adjacent cell. Be aware that if you change the hyperlink, then this formula cell won't change until your workbook calculates. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =GetHyperlinkScreenTip(a1) DavidM wrote: Hi In Column H, I have webpage hyperlinks. In edit the screentip as some text, some between Brackets (***) I'd like to run a macro that goes down column H and gets all the chacracters between and put them in column C. So H1 would go into C1 Etc. Thanks in advance Dave -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to turn off hyperlink screentips? | Links and Linking in Excel | |||
Customizing the Screentips | Charts and Charting in Excel | |||
ToolTips or ScreenTips | New Users to Excel | |||
Screentips ... | Excel Programming | |||
Copy all Screentips | Excel Discussion (Misc queries) |