form cell question in Ver2000

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
0
James
9/30/2005 6:17:18 AM
excel 39879 articles. 2 followers. Follow

4 Replies
618 Views

Similar Articles

[PageSpeed] 8

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)


James wrote:
> 
> 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

-- 

Dave Peterson
0
petersod (12004)
9/30/2005 12:09:45 PM
Having very limited VB exposure is GetURL a library command within VB?

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

James

Dave Peterson wrote:
> 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)
> 
> 
> James wrote:
> 
>>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
> 
> 
0
James
9/30/2005 4:42:42 PM
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 wrote:
> 
> Having very limited VB exposure is GetURL a library command within VB?
> 
> My real programming days ended before MS was a major player.
> 
> James
> 
> Dave Peterson wrote:
> > 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)
> >
> >
> > James wrote:
> >
> >>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
> >
> >

-- 

Dave Peterson
0
petersod (12004)
9/30/2005 8:20:04 PM
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

"James" <JT@9b01.com> wrote in message
news:11jqqni3q7sgs7d@corp.supernews.com...
> Having very limited VB exposure is GetURL a library command within VB?
>
> My real programming days ended before MS was a major player.
>
> James
>
> Dave Peterson wrote:
> > 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)
> >
> >
> > James wrote:
> >
> >>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
> >
> >


0
jcollins (11)
10/1/2005 12:02:32 AM
Reply:

Similar Artilces:

Tricky Freeze Panes question
Hi all Excel Wiz's out there! This is what I want to do: I would like to have cells A1-A22 static, always visible, and still be able to scroll horizontally and vertically. I have tried to do this with freeze panes but can't get it to work. Thankful for any help! Martin Martin To freeze A1:A22 select B23 and Window>Freeze Panes. Note: you cannot freeze just a column without freezing the rows also. Rows 1 through 22 will also be frozen. Which is maybe why you state "can't get it to work". Gord Dibben Excel MVP - XL97 SR2 & XL2002 On 31 Jul 2003 19:40:04 ...

Reservation Form
I have an active website for my business. I built it in Frontpage 2002. It no longer will send email through the settings I used ( Reservation form) and I cannot get it to take any of the settings I give it. I do not think that my knowledge is sufisticated enough. And, I don't think that I am entering the information completely enough. I am using a simple form with a single enter button. Can you help? May be possible. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage 2002 on windows XP" <FrontPage200...

Auto populate a form form an open form
Hi I have a main form with customer names and addresses and would like to add a control button on this form which opens up another form which is used for recording telephone calls on. What I have been trying to do is auto populate this call log form with information from the main form ie name and address? Any thoughts Thanks Chris Chris, Please don't re-post the same question twice. You have to be patient and wait for a reply. Responders are volunteers, and you may not get an answer right away. Posting twice causes some people to respond to the first post, while others are ...

deleting entire rows with the same cell value in the first column
Hi! I have an huge table in which there are lots of rows that have the same value as the one in the row before: 1 1 1 2 2 2 How can I delete them leaving this way only one row per value without searching for them and deleting them manualy? Any help welcome! Pedro, assuming your data starts in a1, in b1 type =if(a1=a2,"Y","") and copy down for the length of your data. Then filter on Y and delete all those rows. HTH -- Sincerely, Michael Colvin "Pedro F." wrote: > Hi! > I have an huge table in which there are lots of rows that have the same >...

Stopping Macro After Certain Cell
I have created a form that staff members have to fill in, the data o this form is submitted to a database. So basically when ever someon fills it in he/she clicks submit and a new row of data is created i the next sheet. How can i disable the macro that does this say after 10 new rows hav been filled in. So when all ten members have filled in there data n more can. The macro will not work. Is there a way to do something like this!! Please help than -- Message posted from http://www.ExcelForum.com Maybe instead of disabling your macro, you could just check to see if the limit had been rea...

Pivot Table question:
If I have a simple spreadsheet, with: acctNo Call Month Number of Calls 23142 Feb-2003 12 23142 Mar-2003 90 25943 Feb-2003 24 25943 Mar-2003 32 26044 Mar-2003 1 1260 Feb-2003 144 1260 Mar-2003 615 25828 Mar-2003 59 17688 Mar-2003 359 5235 Mar-2003 4 23042 Mar-2003 53 Then I can very easy generate a pivot table like this: Sum of Number of Calls Call Month acctNo 2003/02/01 2003/03/01 (blank) Grand Total 1260 144 615 759 5235 4 4 17688 359 359 23042 53 53 23142 12 90 ...

Count cells based upon criteria in other cells
The cells in column J contains donations made by various individuals. Cells in column R contain certain member classifications. Am looking for a formula that looks at column R, then counts the number of entries in the corresponding cells in colum J for each member classsification. EX: in column R there are three classifications: RB1, RB2, RB3. Need to count how many donations in column J are made by each classification. Many thanks! Try the below formula =COUNTIF(J:J,"RB1") -- Jacob "JT" wrote: > The cells in column J contains donati...

What is the purpose of 'Dummy' window in the Command forms
I am wondering why GP has Dummy window with title "~internal~" for the Command forms. For example Command_Financial form has Dummy window Do I need to create Dummy window for my Command forms. If so why I need it. Any comments appreciated!!! Hi Mandeep, The Dummy window is there as a placeholder. It really doesn't do anything, but should be there in order for the form to work properly since your form needs to be open for the commands to be available. The title ~internal~ just prevents this window from showing up in the Security assignment windows. Make sure that...

form pozition on screen
I would like to find form position on screen. It's similar to DoCmd.movesize, but I want discover "parameters' for form position (left corner, top corner, etc) Are you saying that you want to find out what the current position IS? If so, you can use something like this: MyFormLeftVariable = Me.Left MyFormTopVariable = Me.Top MsgBox "My Form is at " & MyFormLeftVariable & VbCrLf & MyFormTopVariable Or however you want to use it. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com ___________...

copy properties Under forms
Hi, I'm wondering if someone can help me with something on copying properties. I've checked the access help but can't find anything there. This is what i want to do. I'm copying text boxes, labels that has caption, name and other information in the properties that i want to keep when i copy the information to another tab on a form. Is there a way i can copy the property information without doing alot of manual work by going into each text or label box. I hope this makes sense. thanks for you help in advance. Keith When you copy a control, everything about the cop...

Cannot open the Insert object (Pdf file) form Excel 2000(9.0.8968 SP-3)
Dear All, I am no idea why I cannot open the embedded object (Pdf file) inside Excel file which using Windows ME. Once clicked the object icons, then it shows the error message likes " the embedded object cannot be opened by the source application" I used the Adobe reader 6.01 as the source application. Actually, I searched the related forum from the Internet. However, I cannot solve the problem. Anyone helps? Thanks tlee Hi, I can't test 2000 in ME, but it works fine in 2003 under XP. -- If this helps, please click the Yes button. Cheers, Shane Devenshire &q...

Protecting a worksheet question
I have a question for Excel 98. I tried to do a simple worksheet protection and provided a password. However, I found that not ALL cell in that particular worksheet was protected. Most of the cell would give you a message telling you the worksheet is protected and you need to unprotect the worksheet to modify the cell content. However, some cells you can still change its content. What are the reasons these things happen? Thank you! Each cell has a locked property that you can toggle. But the "lockedness" doesn't really do anything until the worksheet is protected. Then l...

Form class module for non-existent form
Hi, After copying a form with a class module, something went wrong. I now have a class module for the new form, but the form no longer exists. I am unable to remove the in-limbo class module. I want to use that name for my form, which is not possible as long as that class module is in the way. How do I get rid of the in-limbo class module? I've tried repairing tha DB as well as "msaccess /decompile" but it hasn't helped. Anything else? Thanks, Kjell Kjell, A class module is not part of a form, but a separate module. It sounds like your 'in-limbo' module is...

Cannot hide cells
I have a spreadsheet that is setup columns A-IV and need to hide columns but after a while get the message "cannot shift cells off sheet" any ideas its driving me nuts Regards Craig How are you hiding the columns? It sounds like you might be adding columns or moving cells to the right to hide them from the view. If you have items in Column IV you can not do this. If you are doing it as<format><columns><hide> about howmany times do you do it before you have the problem. (I cant make it happen on my computer) If you are doing it another way, What is it? &q...

Outlook 2003 Live questions
With an Outlook Live subscription, will I be able to keep my desktop and notebook synchronized? I am monitoring up to 16 e-mail accounts related to our website (webmaster, newsletter, unsubscribe, subscribe etc.) in addition to my business e-mail and my personal e-mail. These are all POP3 accounts hosted on 3 different servers. a) can I choose to monitor some accounts via Outlook Live and some through regular Outlook 2003 (which will not be synchronized between the 2 pc's)? b) is there a limit to how many POP3 accounts I can use in Outlook Live? c) can I send e-mail from an...

Go Immediately To A Particular Cell After Inputting Data In Another Cell And Hitting Enter
I'm inputting data in E3 and after I hit enter I want B2 to be selected. But only for E3 and only in one worksheet. And possibly a further step... Sometimes the formula in B2 has picked up data from another place depending on what value was entered in E3. If it's done this then I won't need to go to B2, I'd want to go to A8 after E3 instead of B2. Put this in the code module of the worksheet that contains the ranges you are working with. Right click the sheet name tab, select View Code from the pop up menu and paste this into the code window that appears on scre...

Setting the Windows Cursor during Form Processing
hiya gurus I would like to force the mouse cursor to immediately become an hourglass when some long processing/quering commences on a form. it appears that it takes some time for Access to show the hourglass and usually only after the user clicks the form a couple of times because there appears to be nothing happening... Is there an easy way to do this from within Access.. I have done it in VB6 using Me.MousePointer = vbHourglass but this does nto work in Access.. any suggestions? cheers Jeff mmmmm Screen.MousePointer = 11 guess this does it? "jeff" <jeff@work...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Position 2nd Form according to 1st Form
I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, I want it to position itself about 15cm form the Left and 20cm from the top of the 1st Form. I am using Access 2003, Thanks. "AY" <AY@discussions.microsoft.com> wrote in message news:CFA3A4B5-36D9-4A20-AB57-601290155335@microsoft.com... >I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, >I > want it to position itself about 15cm form the Left and 20cm from the top > of > the 1st Form. I am using Access 2003, Thanks. You should be able to use Nicole Calinoiu...

TOURNAMENT FORMS
Need tournament draw sheet single elimination template. You may find some old NCAA basketball tournament forms out on the web. Try http://google.com HHSTENNIS wrote: > > Need tournament draw sheet single elimination template. -- Dave Peterson ec35720@msn.com ...

Run Macro only if certain form is open
I have programed a maco to open one of various forms depending on criteria on an open form. Is there a way to run a "follow up Macro" based on which form opens? In other words can I run a macro based on wheather a certain form is active? -- Thanks, Chuck Take a look at the RunMacro Method of the DoCmd object. You can have it as part of the OnLoad of the form in question. Chuck wrote: >I have programed a maco to open one of various forms depending on criteria on >an open form. Is there a way to run a "follow up Macro" based on which form >opens? In other w...

page break on every cell
Hi all - I've had some trouble recently when opening Excel 2002 spreadsheets in Excel 2003. When I go to page break preview, every cell is surrounded by page breaks, essentially making every cell one page. I can't reset the print area to fix this - I have to close out of the spreadsheet without saving changes. This doesn't happen when I open the spreadsheet in Excel 2002. It could also be related to opening the document on a Windows XP computer (has the problem) vs. Windows 2000 Pro (no page break problem). Its very odd and makes printing quite difficult. I have the...

Modal form opens on startup
Evenin' - I hope this doesn't come across as stupid, but I'm almost completely unfamiliar with Access. A client of mine has an Access 2000 database containing some business information, and he'd like to have me import it into Quickbooks. The problem I have is that I cannot get to the information to export it from Access. When the file is opened, I get a (I presume) modal form, configured as a 'splash screen' type of thing. Has my client's logo, and the name of the fellow who wrote the Access app. Click that, and it closes, then opens another form for...

Forms and Input Data
I created a report form on Sheet 1 and a set of input data on subsequen Sheets 2, 3, ... link to the report. The report is to show informatio on either of the data I select. Are there simple steps for me to pick either set of data I want t display on the report? I've thought of a pull-down menu so that if select "1", the report will show data set "1", and so on. Yet I don' know how to do it. I tried the "replace" command by highlighting al the cell in the Report sheet then type "2" in place of "1" to replac the data set then hitting...

Trying to delete form border, and it deletes all borders
I made a form with several individual areas that have borders. At the end I decided to try a border around the whole form. Now that I don't like it, I was trying to delete the form border, but it deletes all the borders in the form as well. How can I just delete the border around the form and not the rest of the individual borders? I'm actually on a different computer, because of the printing, will I be able to undo my way out of it maybe, or do I have to be on the computer where I made the form. One more question, will the form retain the undo feature for ever, or will it stop ...