Apply error trap to all objects in worksheet?

[plh]

Hi Gurus,
Say there are a number of pictures on a worksheet. Each has a hyperlink.
Clicking the picture opens the hyperlink. These hyperlinks are such that the
action results in an error to the effect that no program is associated with the
file. It would be trivial to trap this error for an individual picture, then add
the code to do whatever. However, what I want to do is create the situation
where the error trap would apply to ALL hyperlinked pictures on the worksheet,
even newly created ones. The result would be that if a user added a new picture,
and set up a hyperlink to it, that new picture would react the same way. Is
there some way to do this and if so, would any of you enlighten me as to the
method?
Thank You!
-plh

 

[EricG]

You can trap the hyperlink events and do the checking there. Either use the
"FollowHyperlink" event of a worksheet, or use the "SheetFollowHyperlink"
event of the workbook (which traps ALL hyperlink events on ALL worksheets).
Whenever the user clicks on a hyperlink, either one or the other of these
events will be triggered, and you can place code in them to check the
hyperlink for an error.

HTH,

Eric

 

[plh]

Thank you for the information, but neither of those routines fired when
following hyperlinks, whether the hyperlink was in a cell or on a picture.
Is there something else I have to invoke prior to invoking those? (Nothing to
that effect in help files.) I placed them in all possible places,: module,
worksheet, workbook.
Thank You,
-plh

 

[EricG]

I placed the following code in the "ThisWorkbook" module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
MsgBox "Here we are!"
End Sub

And I place a hyperlink in a cell on "Sheet1". When I clicked on the
hyperlink, the MsgBox statement was triggered. The hyperlink was also
triggered, and Internet Explorer opened up to the link I put into the cell.

However, the same thing did not happen when the link was attached to an
image I also placed on "Sheet1". I'm not sure why a hyperlinked image would
be different, but the SheetFollowHyperlink event does not seem to track it.
Perhaps the application level event would catch a hyperlink attached to an
image? I'll look into it.

Eric

 

[plh]

Eric,
I really do appreciate how your fast and informative responses.
I did exactly that, pasting your routine in the "ThisWorkbook" module, but it
did NOT fire. HOWEVER, that was in the case of a file which could not open
because the extension was not recognized by Windows. When I used a hyperlink
that COULD open, the routine DID fire. This creates a problem in my case,
because I need to get something to fire in response to attempts to open
hyperlinks that CANNOT be opened for the reason mentioned above. The idea is to
paste the name of the file to the Windows clipboard for use in another
application. The Workbook is for use by users with reasonable but not unlimited
computer ability. Ideally, they paste a picture into the workbook then add a
hyperlink to it. They have enough ability to do that. However, they will not be
able to add any code. So I need to make it work in response to all hyperlinks
including newly created ones, as I said below.
A little background, who knows, it may help:
The purpose of the application is to allow CNC machine tool programmers to
assemble complete programs quickly from a series of program modules having the
code necessary for common clusters of machined features. Once the information is
on the Windows clipboard they will Alt/Tab to the (Japanese non-Windows based)
CNC programming tool and paste it into the then open search box (in spite of not
being Windows it does employ the Common Dialog Box in some places) which will
lead them to the correct module. In this way the modules can be identified with
pictures. In the CNC programming tool they are identified by names which are
limited as to the number of characters. They can the glance at the sheet, pick
what they need and locate the correct module file without delay. There may be
other ways to accomplish this but I am struggling to keep it as simple and
visual as possible. The overarching objective is to speed up CNC programming of
"one-off" component parts in a situation where management is complaining about
the amount of time it takes to create programs (I know... I know... but what can
one do?). Although the components are "one-off" they contain many commonly
shared features, so there is potential to avoid "re-inventing the wheel" as they
say nowadays.
Thank You,
-plh