Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default 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
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
How to turn off hyperlink screentips? [email protected] Links and Linking in Excel 1 October 26th 07 12:50 AM
Customizing the Screentips M in Oakville Charts and Charting in Excel 1 July 22nd 06 10:22 PM
ToolTips or ScreenTips Stuart Grant New Users to Excel 3 September 30th 05 04:40 PM
Screentips ... SIGE Excel Programming 1 April 27th 05 07:09 PM
Copy all Screentips brmauer Excel Discussion (Misc queries) 1 April 21st 05 05:13 AM


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