form cell question in Ver2000

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have a large sheet with email addresses in a column that only displays
"yes" with the letters in a gray shaded area. When I hover over the
cell the underlying email address becomes visible. I need to find a way
to create a column of the email addresses. Any help will be appreciated.

James
 
It sounds like someone added hyperlinks to your worksheet.

If you select the cell and do Insert|Hyperlink, do you see the email address in
a nice dialog?

If yes, you can use a user defined function to extract that link (including
email address).

Saved from a previous post:

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(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:
=getURL(a1)
 
Having very limited VB exposure is GetURL a library command within VB?

My real programming days ended before MS was a major player.

James
 
It's a macro written in VBA (visual basic for applications).

There were instructions at the bottom of my first response that told how to
implement it.
 
James,

getURL is a new spreadsheet function that you create. Once you have created
it, you use it like other spreadsheet functions such as sin(), cos(),
atan(), etc.

To create the function, follow Dave's instructions. It is simpler and less
daunting than it might sound.

John
 
Back
Top