(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the
sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

Thanks.
I'll use the SelectionChange to simulate hyperlink behaviour.
Once puzzle: I have the following event, but when I click on a hyperlink
nothing happens.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
sStr = "C:\Documents and Settings\Darren\My Documents\My
Pictures\01.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

I've put a breakpoint in the code to test of the event is actually firing,
and it isn't. Any ideas?


--
Darren
"Tom Ogilvy" wrote in message
...
There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the
sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

I'm embarrassed to admit - the puzzle below was because I was in design mode
by accident. Oops :)

But another problem - I created my hyperlinks with VBA. They were definitely
absolute references then. But now they same to have changed to relative
references and so the followhyperlink event brings up an error.

Is there a way to force Hyperlinks to remain as absolute references?

--
Darren
"Darren Hill" wrote in message
...
Thanks.
I'll use the SelectionChange to simulate hyperlink behaviour.
Once puzzle: I have the following event, but when I click on a hyperlink
nothing happens.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
sStr = "C:\Documents and Settings\Darren\My Documents\My
Pictures\01.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

I've put a breakpoint in the code to test of the event is actually firing,
and it isn't. Any ideas?


--
Darren
"Tom Ogilvy" wrote in message
...
There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the
sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that
would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

I haven't run up against this although I don't do much with hyperlinks.
Where are you hyperlinking to? Post your code that creates the hyperlink.

How are you determining that it is now relative - are you editing it
manually or using code to pull the attribute values for address and
subaddress?

Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
I'm embarrassed to admit - the puzzle below was because I was in design
mode
by accident. Oops :)

But another problem - I created my hyperlinks with VBA. They were
definitely
absolute references then. But now they same to have changed to relative
references and so the followhyperlink event brings up an error.

Is there a way to force Hyperlinks to remain as absolute references?

--
Darren
"Darren Hill" wrote in message
...
Thanks.
I'll use the SelectionChange to simulate hyperlink behaviour.
Once puzzle: I have the following event, but when I click on a hyperlink
nothing happens.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
sStr = "C:\Documents and Settings\Darren\My Documents\My
Pictures\01.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

I've put a breakpoint in the code to test of the event is actually
firing,
and it isn't. Any ideas?


--
Darren
"Tom Ogilvy" wrote in message
...
There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of
the
sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that
would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The
following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

You asked
How are you determining that it is now relative - are you editing it
manually or using code to pull the attribute values for address and
subaddress?

I was using the Address property not SubAddress, in the following code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
sStr = Target.Address
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub


Then when the code faulted at the Me.Imagebox line, I used the Locals window
to check the value of of sStr, and it was reported as a relative reference,
for example
"..\..\..\My Pictures\01.jpg"

What's the difference between Address and SubAddress?

I've posted the code used to build the hyperlinks below, but don't put
yourself out trying to solve the problem if it's not obvious. I'm not using
hyperlinks in this project anymore, but I'm curious because I may want to
use Hyperlinks in the future.

THE CODE
(For clarity, I've put ======= around the areas directly related to building
the hyperlinks).

Macro: CreateHyperlinkFileList - this calls "CreateFileList" to create an
array of addresses. It then loops through the array, extracts data from the
picture file names (they are named in a very particular pattern), and then
creates the hyperling (the second set of "===").
The CreateFileList macro: simply builds the array of addresses. I'm not
entirely sure how it does this ;) since I copied it from a website, but it
works.


================
================
Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.FileName = FileFilter
.SearchSubFolders = IncludeSubFolder
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
FileList(FileCount) = .FoundFiles(FileCount)
Next FileCount
.FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
CreateFileList = FileList
Erase FileList
End Function
================
================
Sub CreateHyperlinkFileList()
Dim FileNamesList As Variant, i As Integer, j As Integer
Dim FileName As String, ArtistName As String, FileAddress As String
ChDir "C:\Documents and Settings\Darren\My Documents\My Pictures"
' activate the desired startfolder for the filesearch
'===================================
FileNamesList = CreateFileList("*.*", True)
'===================================
' performs the filesearch, includes any subfolders
' present the result
Range("A:B").Clear
For i = 1 To UBound(FileNamesList)
' create hyperlinks here
FileAddress = FileNamesList(i)
FileName = FileOrFolderName(FileAddress, True)

j = 0
While InStr(j + 1, FileName, "_") 0
j = InStr(j + 1, FileName, "_")
Wend
ArtistName = Left(FileName, j - 1)
If ArtistName = "" Or ArtistName = "_" Then ArtistName = "Unknown"
FileName = Right(FileName, Len(FileName) - j)
FileName = Left(FileName, InStr(FileName, ".") - 1)

With ActiveSheet
.Range("a" & i + 1).Formula = ArtistName
'=============================================
.Hyperlinks.Add Anchor:=.Range("b" & i + 1), _
Address:=FileNamesList(i), _
ScreenTip:=ArtistName, _
TextToDisplay:=FileName
'=============================================
End With
Next i
Columns("A:B").EntireColumn.AutoFit
Columns("A:B").HorizontalAlignment = xlLeft
Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom

End Sub

Are you in Design Mode?

The following worked for me:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
MsgBox "in followhyperlink " & Target.SubAddress
sStr = "C:\Documents and Settings\" & _
"ogilvtw\My Documents\My Pictures\Part1.jpg"
Me.Image1.Picture = LoadPicture(sStr)
End Sub


--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
Thanks.
I'll use the SelectionChange to simulate hyperlink behaviour.
Once puzzle: I have the following event, but when I click on a hyperlink
nothing happens.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String
sStr = "C:\Documents and Settings\Darren\My Documents\My
Pictures\01.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

I've put a breakpoint in the code to test of the event is actually firing,
and it isn't. Any ideas?


--
Darren
"Tom Ogilvy" wrote in message
...
There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the
sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that
would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren