[RESOLVED] n00b Needing help.

[Jenewine]

Here is the thing. This is the best way to give u an example.: http://aaahcs.com/excel/test4paf.xlsx In the (D) column you see it says "click to email". I dont want it to say that, i want it to say tha actual email address that it is supposed to say. What ive been doing is right click D1, left click edit hyperlink and then copy email address to the TEXT TO DISPLAY area. then click. But this is getting strenuous. Because i need to do 4000 of those.
I saw a code that looked like it might work, but i couldnt edit it to make it work. So can someone pls help me.

Sub MakeEmails()
For Each e_address In Range("A1:A45")
ActiveSheet.Hyperlinks.Add Anchor:=e_address, Address:="mailto:" & e_address.Value, TextToDisplay:=e_address.Value
Next
End Sub

matter fact i just noticed, i need it to go backwards. I need the TEXT TO DISPLAY to display whats in the Email Address . Preferably without the mailto: in it.im trying to reverse it but dont know how.

[baja_yu]

Welcome to the forum!

Where are you coding this? Do you have actual VB6 IDE (Visual Studio) or is this VBA within MS Excel?
I'm asking because there is a difference between the two. This section is for VB6, but if you are working with VBA a moderator will move the thread (so you don't need to post again) to the Office Development section where you'll get help quicker.

[Jenewine]

im using the M$ vb inside of M$excel.
I also have VB installed on my computer, but thats not what im using. The one im using is within M$Office.

[akhileshbc]

im using the M$ vb inside of M$excel.
I also have VB installed on my computer, but thats not what im using. The one im using is within M$Office.

That's called VBA (Visual Basic for Application). So you have posted in the wrong forum. I will inform a mod to move this thread to the appropriate section, where you will gt more replies...

This section deals with VB6 and earlier versions only.

Good luck...

[si_the_geek]

Thread moved to 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

(thanks for letting us know akhileshbc )

[westconn1]

what is wrong with the code in the op?
it appears to work correctly to do what you want, i tested it in excel 2000
will error on any empty cells

[Jenewine]

its backwards.. it fills the email area with the text to display parameters.
I wanted it the other way round. Fill the text to display area with the email parameters

[westconn1]

I wanted it the other way round. Fill the text to display area with the email parameters

i am not sure i understand, what you want to achieve, as is,
the email address is displayed in the cell, when clicked an email form is opened for filling

what parameters are you wanting to use?

[Jenewine]

ok.. open the excl file i attached above.
in the (D) column it shows as a hyperlink that says "CLICK TO EMAIL"
if u right clikc on one of those (D) tabs, and click edit hyperlink. The (EMAIL AREA has "mailto:[email protected] And the (TEXT TO DISPLAY) area has "CLICK TO EMAIL
Well, i want where it saays "CLICK TO EMAIL" i want that to say "[email protected].

So variably, TEXT TO DISPLAY field should equal the EMAIL ADDRESS
The script i posted in the beging is the other way around.
Instead is makeing the EMAIL ADDRESS equal TEXT TO DISPLAY

If script works fine, it should still remain a hyperlink. That doesnt change. The thing is, now you can print a sheet and give it to someone, and they would have the email address as well.

Did i explain it better.?

[westconn1]

Did i explain it better.?

a bit, i thought you were already displaying an email that was not a hyperlink
i can not open your file as i do not have office 2007, still using 2000

try like

For Each e In Range("A6:A45")
e.Hyperlinks(1).TextToDisplay = Mid(e.Hyperlinks(1).Address, InStr(e.Hyperlinks(1).Address, ":") + 1)
Next

change range to suit

[Jenewine]

It works... Perfect.! All Done. Thanks.!