Activate Hyperlinks - Excel 2002

[Guest]

Hello,

I thought this would be simple - but I'm beginning to wonder. . .

I have a large "client contact" sheet with columns for "Email" and
"Website", but for some reason the email address and website addresses are
not active hyperlinks.

Short of right clicking on each cell (2600!) - is there a way to highlight
the column and perform this action - PLEASE I HOPE SO!

P.S. I am a general user - not advanced - don't know how to do macros.

Thank you,

 

[Ron de Bruin]

Hi

In 2002-2003 you can turn automatic hyperlinking of
Tools>Autocorrect options

But you can use a macro to do this

With E-mail addresses in column B

Sub test()
For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub


With websites in column C (http://www.rondebruin.nl/tips.htm)

Sub test2()
For Each myCell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If myCell.Value Like "*www*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:=myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub

 

[Guest]

Brilliant Ron!

I don't know how to do Macros - but can you just walk me through it?

Tools, Macros, then what? It looks like I can just cut and paste your
macros in changing my column number correct?

Many thanks - looking for steps to create macros.

Whyvon

 

[Ron de Bruin]

Good morning

Where do I place the macro's ?
1. Alt-F11
2. Insert>Module from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

Tools, Macros, then what? It looks like I can just cut and paste your
macros in changing my column number correct?

Correct

 

[Guest]

Thank you -

It works beautifully but here's the problem:

After I closed it - and deleted it - so it wouldn't cause my client any
trouble - it gave a warning when I went to reopen it. I don't want my client
to see this.

No way around that I suppose?

 

[Dave Peterson]

Are you writing about the warning that the workbook contains macros?

If yes, then there are some remnants that you missed.

Visit Debra Dalgleish site for instructions:
http://www.contextures.com/xlfaqMac.html#NoMacros

 

[Guest]

Thanks both of you very much.

I have since found another solution. . .

but it was rather tedious.

For those who are interested and don't want to do macros you just double
click on each cell and that activates it.