I'm using the Selection Change event. This way I click on the row containing
an image's data, and it changes to that image.


"Tom Ogilvy" wrote in message
...
I can't tell you that. Record a macro setting up the hyperlink manually
and
see how it gets set up. As an example, in the same workbook, the Address
isn't used.

Since there isn't any scroll event, what event are you using to position
your image control?

--
Regards,
Tom Ogilvy

"Darren Hill" wrote in message
...
Aha, I figured out the scrolling thing using the VisibleRange property.
The only mystery remaining: the difference between Address and
SubAddress.
When should I use the latter?

--
Darren
"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