I have a number of hyperlink in my spreadsheet that links to pdf files on the
net. Users often select these accidently when navigating the spreadsheet. I
want to change my spreadsheet so that it requires users to double-click for
the hyperlinks to work. How do I do this?

Very easy. If you enter:

http://www.cnn.com

in a cell, Excel will generate a "click-able" hyper link if you have enabled
this with:

Tools > Options > Spelling > AutoCorrect > Autoformat as you type
and check the hyperlink box.

You don't need to change this option.

Enter the URL with a single quote (apostrophe) in front:

'http://www.cnn.con

and then enter this Event macro in worksheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
ActiveWorkbook.FollowHyperlink Address:=Target.Value
End Sub


so a single click will do nothing, but double-click and away you go!


REMEMBER: worksheet code.
--
Gary's Student
gsnu200709


"AucklandAssault" wrote:

> I have a number of hyperlink in my spreadsheet that links to pdf files on the
> net. Users often select these accidently when navigating the spreadsheet. I
> want to change my spreadsheet so that it requires users to double-click for
> the hyperlinks to work. How do I do this?

On Mar 6, 12:58 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Very easy. If you enter:
>
> http://www.cnn.com
>
> in a cell, Excel will generate a "click-able" hyper link if you have enabled
> this with:
>
> Tools > Options > Spelling > AutoCorrect > Autoformat as you type
> and check the hyperlink box.
>
> You don't need to change this option.
>
> Enter the URL with a single quote (apostrophe) in front:
>
> 'http://www.cnn.con
>
> and then enter this Event macro in worksheet code:
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> ActiveWorkbook.FollowHyperlink Address:=Target.Value
> End Sub
>
> so a single click will do nothing, but double-click and away you go!
>
> REMEMBER: worksheet code.
> --
> Gary's Student
> gsnu200709
>
>
>
> "AucklandAssault" wrote:
> > I have a number of hyperlink in my spreadsheet that links to pdf files on the
> > net. Users often select these accidently when navigating the spreadsheet. I
> > want to change my spreadsheet so that it requires users to double-click for
> > the hyperlinks to work. How do I do this?- Hide quoted text -
>
> - Show quoted text -

Won't that try to fire every time a cell is double clicked, and not
just the ones that are links giving you an error when you double click
a cell that's not an actual address?

On Mar 6, 1:06 pm, "okrob" <merobwhoare...@yahoo.com> wrote:
> On Mar 6, 12:58 pm, Gary''s Student
>
>
>
>
>
> <GarysStud...@discussions.microsoft.com> wrote:
> > Very easy. If you enter:
>
> >http://www.cnn.com
>
> > in a cell, Excel will generate a "click-able" hyper link if you have enabled
> > this with:
>
> > Tools > Options > Spelling > AutoCorrect > Autoformat as you type
> > and check the hyperlink box.
>
> > You don't need to change this option.
>
> > Enter the URL with a single quote (apostrophe) in front:
>
> > 'http://www.cnn.con
>
> > and then enter this Event macro in worksheet code:
>
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
> > ActiveWorkbook.FollowHyperlink Address:=Target.Value
> > End Sub
>
> > so a single click will do nothing, but double-click and away you go!
>
> > REMEMBER: worksheet code.
> > --
> > Gary's Student
> > gsnu200709
>
> > "AucklandAssault" wrote:
> > > I have a number of hyperlink in my spreadsheet that links to pdf files on the
> > > net. Users often select these accidently when navigating the spreadsheet. I
> > > want to change my spreadsheet so that it requires users to double-click for
> > > the hyperlinks to work. How do I do this?- Hide quoted text -
>
> > - Show quoted text -
>
> Won't that try to fire every time a cell is double clicked, and not
> just the ones that are links giving you an error when you double click
> a cell that's not an actual address?- Hide quoted text -
>
> - Show quoted text -

You should add the line
On Error Resume Next

or handle the error some other way.

Rob

Hi Rob:

You are 100% correct.

It all depends on how the user's worksheet is structured. If the
pseudo-links are in a single column, then we could test intersect target and
that column.

We could test PreFixCharacter for the target.

As you pointed out, On Error should also be used.

Additionally Cancel should be handled as well to avoid unnecessary edit
issues.
--
Gary''s Student
gsnu200709


"okrob" wrote:

> On Mar 6, 1:06 pm, "okrob" <merobwhoare...@yahoo.com> wrote:
> > On Mar 6, 12:58 pm, Gary''s Student
> >
> >
> >
> >
> >
> > <GarysStud...@discussions.microsoft.com> wrote:
> > > Very easy. If you enter:
> >
> > >http://www.cnn.com
> >
> > > in a cell, Excel will generate a "click-able" hyper link if you have enabled
> > > this with:
> >
> > > Tools > Options > Spelling > AutoCorrect > Autoformat as you type
> > > and check the hyperlink box.
> >
> > > You don't need to change this option.
> >
> > > Enter the URL with a single quote (apostrophe) in front:
> >
> > > 'http://www.cnn.con
> >
> > > and then enter this Event macro in worksheet code:
> >
> > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > > Boolean)
> > > ActiveWorkbook.FollowHyperlink Address:=Target.Value
> > > End Sub
> >
> > > so a single click will do nothing, but double-click and away you go!
> >
> > > REMEMBER: worksheet code.
> > > --
> > > Gary's Student
> > > gsnu200709
> >
> > > "AucklandAssault" wrote:
> > > > I have a number of hyperlink in my spreadsheet that links to pdf files on the
> > > > net. Users often select these accidently when navigating the spreadsheet. I
> > > > want to change my spreadsheet so that it requires users to double-click for
> > > > the hyperlinks to work. How do I do this?- Hide quoted text -
> >
> > > - Show quoted text -
> >
> > Won't that try to fire every time a cell is double clicked, and not
> > just the ones that are links giving you an error when you double click
> > a cell that's not an actual address?- Hide quoted text -
> >
> > - Show quoted text -
>
> You should add the line
> On Error Resume Next
>
> or handle the error some other way.
>
> Rob
>
>