Hyperlinks in Excel 97

[relative_virtue]

Am trying to make my Excel 2003 worksheet compatible with Excel 97,
and have already hit the first snag. The spreadsheet allows users to
virtually "attach" documents to it, which are later sent in the
background as Lotus Notes attachments. All Excel does is open a file
dialog when "ADD" is clicked in a certain cell and then paste the name
of the selected file on a hidden worksheet.

To do this, I use the following bits of code, neither supported in
Excel 97:

Cells.Hyperlinks.Add anchor:=ActiveSheet.Range(.Address), Address:="",
ScreenTip:="Click here to attach document to spreadsheet",
TextToDisplay:="ADD"

.. . . and. . . .

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)

If Not Intersect(Target.Parent, Range(Target.Parent.Address)) Is
Nothing Then
AddDocs Target.Parent
End If

End Sub

The Hyperlinks object in Excel 97 doesn't seem to support
TextToDisplay, which is a problem, as I obviously can't use an actual
URL here. Likewise, _FollowHyperlink does not seem to be a valid
workbook/sheet event.

Am willing to consider alternatives - can anyone else think of a way
to trap a single mouse click on a cell? Or does anyone know a way to
"fix" the '97 Hyperlinks object?

Grateful for any help,

Tristan

 

[Guest]

Both are correct.

You can replace the texttodisplay with

Cells.Hyperlinks.Add anchor:=ActiveSheet.Range(.Address), Address:="",

ScreenTip:="Click here to attach document to spreadsheet"

Activesheet.Range(.Address).Value = "ADD"

I would just use the selection change event to react to the click.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim hlnk as Hyperlink
if target.count > 1 then exit sub
if Target.hyperlinks.count <> 0 then
adddocs Target
End if
End Sub

I don't have Excel 97 handy to see if the selectionchange fires on selection
of a cell with a hyperlink, but I believe it does. If not, then you could
remove the hyperlink and just format the cell to have Blue underlined text
(although I am not sure what information you are getting from the hyperlink).

 

[relative_virtue]

Thanks Tom,

I rewrote my code as per your instructions and it works great. The
only functionality I've lost is the tooltip, which is no biggie.
Using the _SelectionChange event isn't ideal (because it will then
read a "click" when the user moves into the cell using the arrow
keys), but it's a good workaround for Excel 8 users. For maximum
usability, I've kept my existing _FollowHyperlink code (which only
executes in Excel 2K or above) as it is and written a bit of code like
the following under _SelectionChange:

If Val(Excel.Version) < 9 And Target.Hyperlinks.Count = 1 Then AddDocs
Target

Works best in 2K+ but still works in 97. An excellent solution!

Thanks,

Tristan