Oh, sad day. I must somehow accept that the mighty Tom Ogilvy is fallible ;)

--
Darren
"Tom Ogilvy" wrote in message
...
I stand corrected - I believe Dick is correct that it is a relative
reference. I guess I haven't been creating hyperlinks to the same drive,
so
I have never had a problem with it.

Sorry about the misinformation.

--
Regards,
Tom Ogilvy

Darren Hill wrote in message
...

I see, so I was wrong about why it was failing - not too surprising :)
By the way, what's the difference between Address and SubAddress? Help
isn't
very helpful.

I've hit another snag. I planned to have the imagebox control reposition
itself at the top and right of the active window, so that it remained
there
as you scroll about the sheet.

The following code does this as long as I don't scroll right.

With Me.ImageBox
.Top = Application.ActiveWindow.Top
.Left = Application.ActiveWindow.Width / 2
.Height = Application.ActiveWindow.Height / 2
.Width = Application.ActiveWindow.Width / 2
End With

But if I scroll to the right, the picture doesn't continue to move
right.
I
need to adjust the ".left" line to take the current window position into
account. Can you show me how to fix it?

Darren

"Tom Ogilvy" wrote in message
...
That isn't a relative reference - it is a method of showing a long
string
in
a limited space - it omits non-essential information.

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
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