Hyperlinks default - not able to change?

[gk.lists]

Way back in Nov 2002 this topic was brought up and responded to by JE
McGimpsey who kindly provided a macro for Office X (as shown below) to
keep Excel from turning all links into live hyperlinks. However, this
macro apparently doesn't work in Office 2004, because I've tried
following JEM's instructions, to no avail.

It SO annoys me that the default is "hyperlink on" because I nearly
never need to do that. And yes, I know how to turn off any one link
individually, but I want them to never turn on in the first place, and
when I inherit a workbook with lots of email links for instance, I want
them off, not on, because it's a total time-sink to go through and
individually turn off each one.

Does anyone have a newer version of how to do this?

Thanks!
Gurukarm
(e-mail address removed)
(e-mail address removed)


quoted text,
from message
http://groups.google.com/group/micr...q=hyperlink+++default&rnum=4#e908572587276c54
:

Or, you could put this macro in the ThisWorkbook module of your
workbook (CTRL-click on the title bar and paste the code below into the
Visual Basic Editor window "ThisWorkbook" that opens. You can delete
the empty Workbook_Open macro if you wish. Then click on the XL icon in
the formula toolbar to return to XL). From then on, hyperlinks will
automatically be removed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
With Target.Hyperlinks
If .Count Then .Delete
End With
End Sub

If you want this feature on all new workbooks, put the macro in an
empty workbook and save it *as a template* in the

Microsoft Office X:Office:Startup:Excel:

folder in your Applications folder with the name "Workbook" (no
extension). This template will then be used for all subsequent new
workbooks (unless you choose a different template).

 

[Geoff Lilley]

Gurukarm:

I was able to get JE's code working correctly. There's a couple of
catches (I got thrown off when I first read the code).

Start with a blank workbook.

Move your mouse up to the workbook name (probably something like
"Workbook1."

CTRL+click (or if you have a two-button mouse, right-click.)

Choose "View Code."

When you get into the VBA window, erase what's there, and paste his code.

Click on the green "x" in the upper-left hand corner of the VBA editor
to go back to Excel.

When you save the file, save it in /Applications/Microsoft Office
2004/Office/Startup/Excel/. Save it *as a template* with the name
"Workbook."

That will take care of any NEW books from getting hyperlinks.

Problem is, that doesn't take care of existent workbooks that you get
from your colleagues that already have hyperlinks in them. For that, I
turned to Mr. Spreadsheet himself, Mr. John Walkenbach (I modified a
little bit what Master did) :

Sub ZapHyperlinks()
Dim z as Long
Dim x as Long
For z = 1 To Application.Workbooks.Count
Workbooks(z).Activate
For x = 1 To ActiveWorkbook.Sheets.Count
Sheets(x).Select
Cells.Hyperlinks.Delete
Next
Next
End Sub

Hope that helps. Have a good one.

Cheers
Geoff

 

[JE McGimpsey]

Way back in Nov 2002 this topic was brought up and responded to by JE
McGimpsey who kindly provided a macro for Office X (as shown below) to
keep Excel from turning all links into live hyperlinks. However, this
macro apparently doesn't work in Office 2004, because I've tried
following JEM's instructions, to no avail.

It SO annoys me that the default is "hyperlink on" because I nearly
never need to do that. And yes, I know how to turn off any one link
individually, but I want them to never turn on in the first place, and
when I inherit a workbook with lots of email links for instance, I want
them off, not on, because it's a total time-sink to go through and
individually turn off each one.

Does anyone have a newer version of how to do this?

The code still works fine in XL 2004. Are you sure you have in in the
ThisWorkbook code module?

However, there are many other ways to do it. Among them:

http://www.mcgimpsey.com/macoffice/excel/nohyperlinks.html

and, to automatically remove hyperlinks from all workbooks,

http://www.mcgimpsey.com/macoffice/excel/nohyperlinks2.html

 

[gkkk04]

Thank you JEM and also to Geoff for responses on this topic. After
posting my query, one of our local experts got back to me by email and
suggested this workaround for removing hyperlinks in docs I've
inherited; customize any toolbar with the hyperlink button, which has a
drop-down menu to select "none" for hyperlink. A whole column, row, or
group of cells can be selected and the drop-down used to de-link the
entries.

For an occasional user such as myself, this is much more efficient than
trying to make macros work - but I'll keep the instructions, in case I
need them in future!

Thank you both again.
Cheers,
Gurukarm