G'day there People,

I'd like to think that my problem is a simple one, however I may
just be attempting something Excel isn't designed for.

I have a single worksheet that I wish to use as a menu into
various Excel & Word documents. Quite simple to do, with hyperlinks to
my local files but I don't wish to allow the "Human Factor" too much
leeway. (My clients' skill levels range from network administrators &
programmers, to the painfully computer illiterate).

To prevent overwriting of my links, I'd like to protect my sheet.
While this is a simple procedure, I've found that when my sheet is
protected the links don't work.

I've tried various combinations of protection, locked & unlocked
cells and the Worksheets(1).EnableSelection property. All I've been able
to do is to either prevent the link from working, or permit full access
to the cell.

The Help files describe the properties simply enough, but I can't
seem to work out whether the cells can, in fact, allow access for use as
a link, but not allow access to amend the cell.

Can someone please clarify this for me? and perhaps offer a
direction?

Thanks for listening,
--
Ken McLennan
Qld, Australia

Ken: This should work. To my knowledge only Word has problems with
hyperlinks in protected documents, not Excel. What version are you running?
Have you tried Ctrl+Click to follow the hyperlink? What method are you using
to create the hyperlink?
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ken McLennan" wrote in message
.. .
G'day there People,

I'd like to think that my problem is a simple one, however I may
just be attempting something Excel isn't designed for.

I have a single worksheet that I wish to use as a menu into
various Excel & Word documents. Quite simple to do, with hyperlinks to
my local files but I don't wish to allow the "Human Factor" too much
leeway. (My clients' skill levels range from network administrators &
programmers, to the painfully computer illiterate).

To prevent overwriting of my links, I'd like to protect my sheet.
While this is a simple procedure, I've found that when my sheet is
protected the links don't work.

I've tried various combinations of protection, locked & unlocked
cells and the Worksheets(1).EnableSelection property. All I've been able
to do is to either prevent the link from working, or permit full access
to the cell.

The Help files describe the properties simply enough, but I can't
seem to work out whether the cells can, in fact, allow access for use as
a link, but not allow access to amend the cell.

Can someone please clarify this for me? and perhaps offer a
direction?

Thanks for listening,
--
Ken McLennan
Qld, Australia

G'day there Anne,

Ken: This should work. To my knowledge only Word has problems with
hyperlinks in protected documents, not Excel. What version are you running?

That's interesting. I've got an upcoming project for Word, too,
but I don't know as yet whether I'll need to work with hyperlinks there.
At least now I have a "heads up" so I'll keep your warning in mind.
Thanks for that.

FYI, I'm using Excel 2002 from Office XP. On, obviously, Windows
XP (Home version). There are no service packs installed.

Have you tried Ctrl+Click to follow the hyperlink? What method are you using
to create the hyperlink?

I can easily follow the hyperlink by the usual "click on the
underlined text" method =). I just tried Ctrl+Click and can safely
report that absolutely zilch occurred while the sheet was protected.
When unprotected I simply made a non contiguous selection, as though
there was no link.

I find that the links work as designed/intended, but only when the
cells aren't protected. I want to protect the cell contents; but when I
tick the "locked" checkbox in the Format Cells dialogue, and protect the
sheet, then I can't select the cell containing the link & hence the link
doesn't work.

At the moment I have the cells containing links "unlocked" and
protect the content via code in the ThisWorkbook module:

With Me.Worksheets(1)
.EnableSelection = xlUnlockedCells
.Protect contents:=True
End With

This allows the links to work, but leaves the contents vulnerable
to corruption.

To protect the link I need to have the "locked" checkbox checked
and hence the cell is not selectable, therefore the link can't be used.

To answer your last question, I just use the Insert Hyperlink
dialogue from the Right Click menu.

Thanks for offering your assistance, it's much appreciated.

--
Ken McLennan
Qld, Australia

Ken, why are you using that method to protect the cells? I think that's the
problem. Just use regular cell locking, and use code to protect/unprotect
the sheet itself. I think that'll resolve your issue.
http://www.officearticles.com/excel/...soft_excel.htm
If you often need to work on the workbook, you can protect/unprotect all
sheets at once using a macro like this one:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

*******************
~Anne Troy

www.OfficeArticles.com


"Ken McLennan" wrote in message
.. .
G'day there Anne,

Ken: This should work. To my knowledge only Word has problems with
hyperlinks in protected documents, not Excel. What version are you
running?

That's interesting. I've got an upcoming project for Word, too,
but I don't know as yet whether I'll need to work with hyperlinks there.
At least now I have a "heads up" so I'll keep your warning in mind.
Thanks for that.

FYI, I'm using Excel 2002 from Office XP. On, obviously, Windows
XP (Home version). There are no service packs installed.

Have you tried Ctrl+Click to follow the hyperlink? What method are you
using
to create the hyperlink?

I can easily follow the hyperlink by the usual "click on the
underlined text" method =). I just tried Ctrl+Click and can safely
report that absolutely zilch occurred while the sheet was protected.
When unprotected I simply made a non contiguous selection, as though
there was no link.

I find that the links work as designed/intended, but only when the
cells aren't protected. I want to protect the cell contents; but when I
tick the "locked" checkbox in the Format Cells dialogue, and protect the
sheet, then I can't select the cell containing the link & hence the link
doesn't work.

At the moment I have the cells containing links "unlocked" and
protect the content via code in the ThisWorkbook module:

With Me.Worksheets(1)
.EnableSelection = xlUnlockedCells
.Protect contents:=True
End With

This allows the links to work, but leaves the contents vulnerable
to corruption.

To protect the link I need to have the "locked" checkbox checked
and hence the cell is not selectable, therefore the link can't be used.

To answer your last question, I just use the Insert Hyperlink
dialogue from the Right Click menu.

Thanks for offering your assistance, it's much appreciated.

--
Ken McLennan
Qld, Australia

G'day there Anne,

Ken, why are you using that method to protect the cells? I think that's the

Why? How the heck should *I* know?!! I'm only writing the code
<grin.

problem. Just use regular cell locking, and use code to protect/unprotect
the sheet itself. I think that'll resolve your issue.

Yes, you're quite right. That's exactly what I needed to do. I'd
tried thinking too hard about it and made the whole issue far more
complex than it needed to be. It doesn't matter if my clients can select
the cells, because if they're locked the contents are still safe. It was
so obvious when you pointed it out.

http://www.officearticles.com/excel/...soft_excel.htm

I shall have a look at that one.

If you often need to work on the workbook, you can protect/unprotect all
sheets at once using a macro like this one:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

...and that one also.

Thanks very much for your assistance, Anne. I really do appreciate
it. Perhaps if you have a spare bucket of Clear Thinking you might like
to package it up and send it to me. Don't forget the stamps =)

Thanks once again,
See ya
--
Ken McLennan
Qld, Australia

Fresh out of spare buckets, Ken. ROFL!!
*******************
~Anne Troy

www.OfficeArticles.com


"Ken McLennan" wrote in message
.. .
G'day there Anne,

Ken, why are you using that method to protect the cells? I think that's
the

Why? How the heck should *I* know?!! I'm only writing the code
<grin.

problem. Just use regular cell locking, and use code to
protect/unprotect
the sheet itself. I think that'll resolve your issue.

Yes, you're quite right. That's exactly what I needed to do. I'd
tried thinking too hard about it and made the whole issue far more
complex than it needed to be. It doesn't matter if my clients can select
the cells, because if they're locked the contents are still safe. It was
so obvious when you pointed it out.

http://www.officearticles.com/excel/...soft_excel.htm

I shall have a look at that one.

If you often need to work on the workbook, you can protect/unprotect all
sheets at once using a macro like this one:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

...and that one also.

Thanks very much for your assistance, Anne. I really do appreciate
it. Perhaps if you have a spare bucket of Clear Thinking you might like
to package it up and send it to me. Don't forget the stamps =)

Thanks once again,
See ya
--
Ken McLennan
Qld, Australia